sql-server - 将 MSSQL 数据库备份还原到新数据库

Question

我需要做一个干净的数据库模板备份，然后用另一个名字生成一个新的数据库，但有备份的内容。备份脚本是:

sqlcmd -S %DB_HOST% -Q "BACKUP DATABASE %DB_NAME% TO DISK = '%BACKUP_FILE%'"

恢复脚本是:

sqlcmd -S %DB_HOST% -Q "RESTORE DATABASE %DB_NAME% FROM DISK = '%BACKUP_FILE%' WITH REPLACE"

并且它在相同的数据库名称上工作。但是，如果我备份数据库“ORIGINAL_DB_NAME”，然后将其恢复到“NEW_DB_NAME”，则会引发异常:

Msg 1834, Level 16, State 1, Server <HOST>, Line 1
The file '.....\MSSQL\Data\ORIGINAL_DB_NAME.mdf' cannot be overwritten.  It is being used by database 'ORIGINAL_DB_NAME'.
....

Answer 1

您需要为数据/日志文件指定一个新位置。

参见 this MSDN article , 例 D

RESTORE DATABASE AdventureWorks2012
FROM AdventureWorksBackups
   WITH NORECOVERY, 
      MOVE 'AdventureWorks2012_Data' TO 
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf', 
      MOVE 'AdventureWorks2012_Log' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH RECOVERY;