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.

Solution 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.

Solution 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

Solution 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

Comments

[WilfriedVS]

The database doesn't exist but the files are still there!

[faester]

Ok, thats another problem :) But if the files exist without being attached to the sql server instance wouldn't it be cleaner to have the installer performing the deletion? -- And if they exist but are attached to another db you probably don't want to overwrite them.

[WilfriedVS]

That's the idea: if the installer runs on a local machine I can delete the files before creating the database. But how do I do this when the user installs to a remote sql server?

[faester]

I would claim that the existence of an mdf file in the sql server data directory on any box means that the file is either attached to a running database which then might be dropped or is left there as a consequence of a detached database and should be deleted by the dba only. In the latter case you could choose to create your database with non standard file names to avoid the problem, but deleting the file does seem like a task that shouldn't be automated. Syntax like: CREATE DATABASE tests ON (NAME = tests_mdb, FILENAME = 'C:\temp\tests.mdf')

[WilfriedVS]

"is left there as a consequence of a detached database" : This is correct (the database of my program). The filename is unique (mydatabase is just an example) This program runs on 20k installations worldwide. there are "dba's" that dont know what detaching means. I must find a way to automate this in the installer.

[faester]

I get your point. If you want to delete the files from inside sql server wouldn't an approach then be to try to reattach the file and then DROP the database it if it exists?

[faester]

I hope you succeed. And please let me (us) know of your progress.

[SqlACID]

@faester Going down that road is filled with landmines; what if one of the files required to do the attach is missing? What if it is detached because it is damaged? Any way you can use a different filename when the collisions occur? Or catch the error and force well-guided manual intervention before continuing? But to directly answer your question, you could use xp_cmdshell, if permissions permit, to delete files from the servers file system.

[faester]

@SqlACID: I agree that it is dangerous and I believe I have stated several times that it would be dangerous to delete files and that I wouldn't recommended. Thanks for mentioning xp_cmdshell :)

[Taylor Liss]

This worked for me. Thank you - I only needed to stop the Server to remove the file.