Create database error: cannot create file

Question

I have an installer which as part of the installation creates a SQL Server 2000 (and up) database.

Some users change database server, detach database, ... and want to reinstall.

If the file exists in the default location I get the following error:

Cannot create file 'C:\Program Files\Microsoft SQL Server...\DATA\mydatabase.mdf' because it already exists.

I can solve this by checking the default path first and deleting the files. But how can I do this when the user installs to a remote SQL Server?

I'm looking for something like:

CREATE DATABASE mydatabase WITH OVERWRITE

Edit:

Drop database is not the solution. The database does not exist but the files are still there.

Answer 1

You can test if the database exists with sys.sysdatabase like this

IF EXISTS (SELECT * FROM sys.sysdatabases WHERE NAME = 'mydatabase')
BEGIN 
   DROP DATABASE mydatabase
END

If you want to test if a specific file is attached to the sql server already, you can also use the system view sys.sysdatabases since it contains the 'filename' attribute containing the mdf file for all databases.

If the file is attached to a different database I think it sounds risky to just overwrite it, and you should probably rather delete the database that is attached to the file. Dropping the database will delete the underlying file.

If the file exists but isn't connected to the sql server you should probably delete it once and make sure that the drops are deleting files on subsequent deletes.

Answer 2

It's 2018 now, and Life and Windows have changed.

So we need a new procedure to get rid of the MDF file

• The database is not in Microsoft SQL Server.
• trying to delete it programmatically does not remove the files because the database does not exist
• It is not possible to delete the MDF in a file explorer, because "it is in use by SQL Server"
• I've tried to use Management Studio to restore the database and then delete it as TajMahals suggested, alas it didn't work.

The proper way to delete the file would be to stop the SQL server, delete the file using a file explorer, then start the server again.

See Start, Stop, Pause, Resume, Restart SQL Server Services

• Using a file explorer go to folder *C:\Windows\SysWOW64*
• Find the file SQLServerManager13.msc The number 13 might be different depending on your version
• Start the program
• On the left window pane select Sql Server Services
• In the right window pane you'll see SQL Server, and probably the Agent and the Browser
• Stop them in the following order: Browser, Agent, Server. Do this by right clicking the item that you want to stop and select stop
• Using the file explorer delete the MDF file that causes the problem
• Start the services in reversed order

And you're done

Answer 3

This happens because somebody might have renamed your database.. but at back .mdf file is named as first time the database was created. You can check database name and its corresponding .mdf file from following command:

SELECT * FROM sys.sysdatabases