SQL Server Database RESTORE WITH MOVE or not WITH MOVE

Problem

A recent tip related to RESTORE operations reminded me of something I’ve wanted to share for quite some time.  I frequently restore production databases to various development servers.  The drive layouts between the servers are almost never the same.  I am constantly shuffling files on my development servers due to space constraints, which in turn breaks the automated restore scripts because the MOVE clauses are invalidated.  Do I have to use the MOVE clause every time I do a restore?

Solution

The short answer is NO…but stopping there wouldn’t make a very worthwhile tip.  So let’s work through an example scenario to illustrate.

Prerequisites

While the code within this tip was developed and tested against a SQL Server 2005 instance, it should function on any version currently in use.  The scenario assumes that the AdventureWorks sample database has been attached.

Disclaimer

The paths used in the code may need to be updated to reflect the folder structure in use by your environment.

Code to test SQL Restore

First, drop the test database “AdventureWorksCopy” used by the RESTORE commands, if it already exists.

USE [master]
GO
IF DB_ID('AdventureWorksCopy') IS NOT NULL
BEGIN
  ALTER DATABASE [AdventureWorksCopy] SET SINGLE_USER WITH
  ROLLBACK IMMEDIATE;
  DROP DATABASE [AdventureWorksCopy];
END

Next, review the current layout of the database files.

USE [master]
GO
SELECT 
  DB_NAME([database_id]) [database_name]
, [file_id]
, [type_desc] [file_type]
, [name] [logical_name]
, [physical_name]
FROM sys.[master_files]
WHERE [database_id] IN (DB_ID('AdventureWorks'), DB_ID('AdventureWorksCopy'))
ORDER BY [type], DB_NAME([database_id]);

The query should only return two rows at this point, one each for the data and log file associated with the AdventureWorks database.

Backup the AdventureWorks database.

Attempt to restore a copy of the AdventureWorks database to AdventureWorksCopy without using the MOVE clause.

An error similar to the following is returned because the RESTORE command is attempting to overwrite files already in use by the source database.

Now execute the restore command using the MOVE clause.

The restore completes successfully as expected.

What if we try the restore again without the MOVE clause?

The restore completes successfully once again, even though we omitted the MOVE clause.

Review the file layout of both databases using the same query as before.

Notice that both databases have the same two file_id values, even though the physical paths are different.

Conclusion

As long as the database being restored already has the same number and type of files to match those contained in the backupset being restored, the MOVE clause is not required.  This functionality offers additional flexibility in automating regularly-scheduled restore operations.

Next Steps

• Review any scheduled restore jobs that may be improved by omitting the MOVE clause where possible.
• Check out other mssqltips related to backup and restore operations.