从同一服务器上另一个数据库的备份创建新数据库？

我试图创建一个新的数据库从旧的数据库备份在同一台服务器上。 当使用 SQL 服务器管理工作室并试图从备份恢复到新的数据库时，我得到了这个错误

System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'test' database. (Microsoft.SqlServer.Smo)

在谷歌了一下之后，我找到了这段代码

    RESTORE DATABASE myDB


FROM DISK = 'C:\myDB.bak'


WITH MOVE 'myDB_Data' TO 'C:\DATA\myDB.mdf',


MOVE 'myDB_Log' TO 'C:\DATA\myDB_log.mdf'
GO

我想知道 move 语句会不会影响备份来自服务器的数据库？

谢谢，非常感谢。

154071 次浏览
小开
最佳答案

What I should to do:

  • Click on 'Restore Database ...' float menu that appears right clicking the "Databases" node on SQL Server Management Studio.
  • Fill wizard with the database to restore and the new name.
  • Important If database still exists change the "Restore As" file names in the "Files" tab to avoid "files already in use, cannot overwrite" error message.

What I do

IDk why I prefer to do this:

  • I create a blank target database with my favorite params.
  • Then, in "SQL Server Management Studio" restore wizard, I look for the option to overwrite target database. It is in the 'Options' tab and is called 'Overwrite the existing database (WITH REPLACE)'. Check it.
  • Remember to select target files in 'Files' page.

You can change 'tabs' at left side of the wizard (General, Files, Options)

小开

Think of it like an archive. MyDB.Bak contains MyDB.mdf and MyDB.ldf.

Restore with Move to say HerDB basically grabs MyDB.mdf (and ldf) from the back up, and copies them as HerDB.mdf and ldf.

So if you already had a MyDb on the server instance you are restoring to it wouldn't be touched.

小开

Checking the Options Over Write Database worked for me :)

enter image description here

小开

It's even possible to restore without creating a blank database at all.

In Sql Server Management Studio, right click on Databases and select Restore Database... enter image description here

In the Restore Database dialog, select the Source Database or Device as normal. Once the source database is selected, SSMS will populate the destination database name based on the original name of the database.

It's then possible to change the name of the database and enter a new destination database name.

enter image description here

With this approach, you don't even need to go to the Options tab and click the "Overwrite the existing database" option.

Also, the database files will be named consistently with your new database name and you still have the option to change file names if you want.

小开

The script in the question is just missing the replace statement so the restore script will be

RESTORE DATABASE myDB


FROM DISK = 'C:\myDB.bak' ,


WITH MOVE 'myDB_Data' TO 'C:\DATA\myDB.mdf',
,
MOVE 'myDB_Log' TO 'C:\DATA\myDB_log.mdf' ,  NOUNLOAD,  REPLACE,  STATS = 5

提交答案