创建数据库错误：无法创建文件

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

我有一个安装程序,作为安装的一部分创建一个SQL Server 2000(和更高版本)数据库。

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.

无法创建文件'C:\ Program Files \ Microsoft SQL Server ... \ DATA \ mydatabase.mdf',因为它已经存在。

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?

我可以通过先检查默认路径并删除文件来解决这个问题。但是,当用户安装到远程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.

Drop数据库不是解决方案。数据库不存在但文件仍然存在。

3 个解决方案

#1

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

您可以测试数据库是否与sys.sysdatabase一样存在

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.

如果要测试特定文件是否已附加到sql server,则还可以使用系统视图sys.sysdatabases,因为它包含包含所有数据库的mdf文件的“filename”属性。

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.

如果文件存在但未连接到sql server,则应该删除一次,并确保drop正在删除后续删除时的文件。

#2

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

现在是2018年,Life和Windows已经改变了。

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

所以我们需要一个新的程序来摆脱MDF文件

The database is not in Microsoft SQL Server.

该数据库不在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 used by SQL Server"

无法在文件资源管理器中删除MDF,因为“它已被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.

我试图使用Management Studio恢复数据库,然后将其删除为TajMahals建议,唉它不起作用。

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.

删除文件的正确方法是停止SQL服务器,使用文件资源管理器删除文件,然后再次启动服务器。

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

请参阅启动,停止,暂停,恢复,重新启动SQL Server服务

Using a file explorer go to folder **C:\Windows\SysWOW64**

使用文件资源管理器转到文件夹** C:\ Windows \ SysWOW64 **

Find the file SQLServerManager13.msc The number 13 might be different depending on your version

查找文件SQLServerManager13.msc数字13可能会有所不同,具体取决于您的版本

Start the program

启动该程序

On the left window pane select Sql Server Services

在左侧窗格中选择Sql Server Services

In the right window pane you'll see SQL Server, and probably the Agent and the Browser

在右侧窗格中,您将看到SQL Server,可能还有代理和浏览器

Stop them in the following order: Browser, Agent, Server. Do this by right click the item you want to stop and select stop

按以下顺序停止它们:浏览器,代理,服务器。通过右键单击要停止的项目并选择停止来执行此操作

Using the file explorer delete the MDF file that causes the problem

使用文件资源管理器删除导致问题的MDF文件

Start the services in reversed order

以相反的顺序启动服务

And you're done

而且你已经完成了

#3

-1

I use SQL Management Studio

我使用SQL Management Studio

Object Explore > choose instance > Right click at DataBase Folder > Restore Database

对象浏览>选择实例>右键单击DataBase Folder>还原数据库

In the Source section select "Device" then choose the .mdf file you want to eliminate (choose extension as .* All File)

在“源”部分中选择“设备”,然后选择要消除的.mdf文件(选择扩展名为。*所有文件)

So, you got the new database with the old MDF file.

因此,您使用旧的MDF文件获得了新数据库。

Then drop it the right way, the .mdf will disappear.

然后以正确的方式放下它.mdf将消失。

cheers.

#1