Error : MDF file cannot be overwritten in SQL Server

Today one of my colleague asked me that he was trying to replace an existing database with another database, while trying to do so , he has encountered the below error , though he was using same restore scripts which were working with out any error  in another machine of same configuration.

SQL Scripts used:

USE master
BACKUP DATABASE [Test_master] TO DISK = N'C:\Backups\Test_master.bak' WITH NOFORMAT, INIT, NAME = N'Test_master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10

ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Test] FROM DISK = N'C:\Backups\Test_master.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

Error Message:


Msg 1834, Level 16, State 1, Line 6 
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master.mdf' cannot be overwritten. It is being used by database 'Test_master'.

Msg 3156, Level 16, State 4, Line 6 File 'Test_master' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master.mdf'. 
Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 6
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master_log.ldf' cannot be overwritten. It is being used by database 'Test_master'.
Msg 3156, Level 16, State 4, Line 6 File 'Test_master_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master_log.ldf'.
 Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 6 Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.

Solution:

As the Error message clearly says that restore should happen WITH MOVE. We were able to restore Successfully by using WITH MOVE.

RESTORE DATABASE [Test] FROM DISK = N'C:\Backups\Test_master.bak' 
WITH MOVE 'Test_master' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf', 
MOVE 'Test_master_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_log.ldf', REPLACE, STATS = 10;

Output of this query:


14 percent processed.
23 percent processed. 
32 percent processed. 
42 percent processed. 
51 percent processed. 
61 percent processed. 
70 percent processed. 
80 percent processed. 
94 percent processed. 
100 percent processed. 
Processed 168 pages for database 'Test', file 'Test_master' on file 1. Processed 2 pages for database 'Test', file 'Test_master_log' on file 1. RESTORE DATABASE successfully processed 170 pages in 0.023 seconds (57.659 MB/sec).
To answer the question of my colleague on why he didn’t receive the same error on another machine with same set of scripts.

lets try with same scripts which he has used. i.e. WITH OUT using MOVE

USE master BACKUP DATABASE [Test_master] TO DISK = N'C:\Backups\Test_master.bak' WITH NOFORMAT, INIT, NAME = N'Test_master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10 

ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

RESTORE DATABASE [Test] FROM DISK = N'C:\Backups\Test_master.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

As we could see that we are able to restore with No Error 🙂


4 percent processed.
23 percent processed. 
33 percent processed. 
42 percent processed.
52 percent processed.
61 percent processed.
71 percent processed.
80 percent processed. 
94 percent processed.
Processed 168 pages for database 'Test_master', 
file 'Test_master' on file 1. 100 percent processed.
Processed 1 pages for database 'Test_master', file 'Test_master_log' on file 1. BACKUP DATABASE successfully processed 169 pages in 0.021 seconds (62.872 MB/sec). 
14 percent processed. 
23 percent processed. 
33 percent processed. 
42 percent processed. 
52 percent processed. 
61 percent processed. 
80 percent processed. 
94 percent processed. 
100 percent processed. 
Processed 168 pages for database 'Test', file 'Test_master' on file 1. Processed 1 pages for database 'Test', file 'Test_master_log' on file 1. RESTORE DATABASE successfully processed 169 pages in 0.021 seconds (62.872 MB/sec).
Conclusion:

As long as the database being restored already has the same number and type of files to match those contained in the backup set being restored, the MOVE clause is not required.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s