Monday, August 29, 2016

SQL: Restore MDF 相沖

Error message:

System.Data.SqlClient.SqlError: The file 'C:\...\SomeDB.MDF' cannot be 
overwritten.  It is being used by database 'SomeDB'. (Microsoft.SqlServer.Smo)

在Restore DB時遇到這個問題,就算强制Overwrite也無法解決。

看來是這個MDF檔案相沖了,然後就在網上找有沒有轉移這個MDF的方法。然後就找到了這段代碼,可以在Restore時把文件更改一下。
USE master
-- SET Database in SINGLE USER
-- Force DISCONNECT all connection
ALTER DATABASE SomeDB
SET SINGLE_USER 
WITH ROLLBACK AFTER 30

-- RESTORE Database 
RESTORE DATABASE SomeDB
FROM DISK = 'H:\SQLBackups\YourBaackUpFile.bak'
WITH REPLACE, 
MOVE 'YourMDFLogicalName' TO 'S:\SQLData\SomeMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'S:\SQLData\SomeLDFFile.ldf'

-- SET database in MULTI User Mode 
ALTER DATABASE SomeDB SET MULTI_USER
GO

Reference: Database restore failed becuse mdf cannot be overwritten. It is being used by another database

No comments: