SQL Server Database RESTORE WITH MOVE or not WITH MOVE

By:   |   Comments (15)   |   Related: > Restore


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.  SQL Server sample databases can be found on codeplex.

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.

restore data

Backup the AdventureWorks database.

USE [master]
GO

BACKUP DATABASE [AdventureWorks] TO DISK = 'c:\mssql\backup\yukon\AW2K5_Full.bak'
WITH CHECKSUM, COPY_ONLY, FORMAT, INIT, STATS = 10;

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

USE [master]
GO

RESTORE DATABASE [AdventureWorksCopy] FROM DISK = 'c:\mssql\backup\yukon\AW2K5_Full.bak'
WITH CHECKSUM, RECOVERY, REPLACE, STATS = 10;

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

Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf' cannot be overwritten. It is being used by database 'AdventureWorks'.

Msg 3156, Level 16, State 4, Line 1
File 'AdventureWorks_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf' cannot be overwritten. It is being used by database 'AdventureWorks'.

Msg 3156, Level 16, State 4, Line 1
File 'AdventureWorks_Log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Now execute the restore command using the MOVE clause.

USE [master]
GO

RESTORE DATABASE [AdventureWorksCopy] FROM DISK = 'c:\mssql\backup\yukon\AW2K5_Full.bak'
WITH CHECKSUM,
MOVE 'AdventureWorks_Data' TO 'c:\mssql\data\yukon\AdventureWorksCopy_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'c:\mssql\log\yukon\AdventureWorksCopy_Log.ldf',
RECOVERY, REPLACE, STATS = 10;

The restore completes successfully as expected.

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 20576 pages for database 'AdventureWorksCopy', file 'AdventureWorks_Data' on file 1.
Processed 1 pages for database 'AdventureWorksCopy', file 'AdventureWorks_Log' on file 1.
RESTORE DATABASE successfully processed 20577 pages in 6.501 seconds (25.929 MB/sec).

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

USE [master]
GO

RESTORE DATABASE [AdventureWorksCopy] FROM DISK = 'c:\mssql\backup\yukon\AW2K5_Full.bak'
WITH CHECKSUM, RECOVERY, REPLACE, STATS = 10;

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.

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]);

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

restore data

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Mike Eastland Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, April 11, 2022 - 3:11:56 AM - Kirsten Back To Top (89988)
Thank you very much, helped me a lot. :-)

Friday, February 5, 2021 - 8:30:37 AM - FMSD Back To Top (88186)
Excelente ayuda.

Gracias.

Tuesday, November 12, 2019 - 7:52:51 PM - Ayyappa Back To Top (83078)

Thank you very much.

Saved lot of time trying to figure out the logical file names of the data and log files.

In my case they were not having _Data prefix for data file and that was causing me the problem.

Wednesday, August 16, 2017 - 9:49:16 AM - NP_STAR Back To Top (64915)

 

 Thank you so much

 

Sunday, March 5, 2017 - 9:54:03 PM - John Back To Top (47338)

 

Update to my findings - apparently the restore does require REPLACE while the target is set to RECOVERY FULL.  I encountered the "tail of the log" error when attempting without REPLACE, so I stand corrected from my earlier assertion.

However, it does still seem viable to omit REPLACE for target databases under SIMPLE recovery.  All database restore targets under SIMPLE recovery have thus far raised no objection when foregoing the REPLACE option.

Saturday, March 4, 2017 - 10:22:56 PM - John Back To Top (47227)

This is a great find to verify my own experimentation - thank you for posting this!

I'd also like to add another wrinkle of further streamlining:  if the database being restored to has the same name as the database from which the backup was taken, even the REPLACE keyword is not necessary!

I even tried altering the target db to generate some log (under both Simple and Full recovery) and still no complaints.  I had expected at least a "tail of the log" error under Full.

So to recap, neither MOVE nor REPLACE appear to be necessary under the following scenario:

  • SERVER_A backup of DATABASE_1 to db1.bak
  • SERVER_B restore of DATABASE_1 from db1.bak

Thursday, May 7, 2015 - 10:35:50 AM - Nate Back To Top (37123)

/**
 * Creates (or resets) a Database to a copy of the template database using backup and restore.
 *
 * Usage: Update the @NewDatabase value to the database name to create or reset.
 */

DECLARE @NewDatabase SYSNAME = 'new_db';

-- Set up
USE tempdb;

DECLARE @TemplateBackups SYSNAME = 'TemplateBackups';
DECLARE @TemplateDatabase SYSNAME = 'template_db';
DECLARE @TemplateDatabaseLog SYSNAME = @TemplateDatabase + '_log';

-- Create a backup of the template database
BACKUP DATABASE @TemplateDatabase TO DISK = @TemplateBackups WITH CHECKSUM, COPY_ONLY, FORMAT, INIT, STATS = 100;

-- Get the backup file list as a table variable
DECLARE @BackupFiles TABLE(LogicalName nvarchar(128),PhysicalName nvarchar(260),Type char(1),FileGroupName nvarchar(128),Size numeric(20,0),MaxSize numeric(20,0),FileId tinyint,CreateLSN numeric(25,0),DropLSN numeric(25, 0),UniqueID uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit,TDEThumbprint varbinary(32));
INSERT @BackupFiles EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @TemplateBackups + '''');

-- Create  the backup file list as a table variable
DECLARE @NewDatabaseData VARCHAR(MAX);
DECLARE @NewDatabaseLog VARCHAR(MAX);

SELECT @NewDatabaseData = PhysicalName FROM @BackupFiles WHERE Type = 'D';
SELECT @NewDatabaseLog = PhysicalName FROM @BackupFiles WHERE Type = 'L';

SET @NewDatabaseData = REPLACE(@NewDatabaseData, @TemplateDatabase, @NewDatabase);
SET @NewDatabaseLog = REPLACE(@NewDatabaseLog, @TemplateDatabase, @NewDatabase);

RESTORE DATABASE @NewDatabase FROM DISK = @TemplateBackups WITH CHECKSUM, RECOVERY, REPLACE, STATS = 100,
   MOVE @TemplateDatabase TO @NewDatabaseData,
   MOVE @TemplateDatabaseLog TO @NewDatabaseLog;

-- Change Logical File Name
DECLARE @SQL_SCRIPT VARCHAR(MAX)='
    ALTER DATABASE [{NewDatabase}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [{NewDatabase}] MODIFY FILE (NAME=N''{TemplateDatabase}'', NEWNAME=N''{NewDatabase}'');
    ALTER DATABASE [{NewDatabase}] MODIFY FILE (NAME=N''{TemplateDatabase}_log'', NEWNAME=N''{NewDatabase}_log'');
    ALTER DATABASE [{NewDatabase}] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
    SELECT name AS logical_name, physical_name FROM SYS.MASTER_FILES WHERE database_id = DB_ID(N''{NewDatabase}'');
';
SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{TemplateDatabase}', @TemplateDatabase);
SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{NewDatabase}', @NewDatabase);
EXECUTE (@SQL_SCRIPT);

Friday, August 15, 2014 - 5:46:37 PM - Gabe Back To Top (34162)

OK.  I tested this and thought I'd post back with my findings so others might learn as I have.

NOTE, I did all of this from the SSMS GUI interface.

1) I created a PRODUCTION_DB and NON_PRODUCTION_DB.

2) Created a table in PRODUCTION_DB called Important_Table, inserted some records, and backed up the DB.

3) Created some more records in Important_Table (simulating new data that could be lost).

4) Detached PRODUCTION_DB and it no longer displayed in Object Explorer

5) Created a table in NON_PRODUCTION_DB called Testing_Table (no data necessary because it's going to be lost)

6) Right-clicked NON_PRODUCTION_DB in Object Explorer, started the Restore DB Wizard

7) Selected the PRODUCTION_DB.BAK file as the source device and chose "Overwrite the existing database (WITH REPLACE)" in the Options menu.  (I did NOT change the Restore As file locations (aka MOVE command), they were pointing to PRODUCTION_DB.MDF and LDF.)

8) The restore finished with no "file in use" error.

9) Queried Testing_Table in NON_PRODUCTION_DB, it doesn't exist (as expected).

10) Queried Important_Table in NON_PRODUCTION_DB, it exists but only with records from the backup (as expected).

11) Tried to attach the PRODUCTION_DB.MDF file and it said it was in use (keep in mind, only NON_PRODUCTION_DB is displayed in the Object Explorer).

12) I detached NON_PRODUCTION_DB (it disappeared from Object Explorer), then was able to attach the PRODUCTION_DB.MDF file.  The database attached and was listed as NON_PRODUCTION_DB in the Object Explorer!

Summary: The PRODUCTON_DB name is gone and NON_PRODUCTION_DB is what it is now called.  The PRODUCTION_DB.MDF/LDF files were overwritten by the BAK file so my new records entered in step 3 are gone.  The NON_PRODUCTION_DB.MDF/LDF files are gone.

This was eye opening for me because I've always just assumed that RESTORE NON_PRODUCTION_DB was pointing the source you select to the NON_PRODUCTION_DB's MDF/LDF files.  Leaning on a script all those years (with the MOVE command actually doing the redirecting) and not understanding what it was truly doing really opened my eyes.  The Wizard's default 'Restore As' locations are PRODUCTION.MDF and LDF and I did not know that until just yesterday...after 9 years :P (I'm such a N00blet).

Mike, your argument is still strong that you don't necessarily need the MOVE commands but in a single instance environment, it's necessary not only to avoid the error of locked files but the potential disaster that I've just learned about because most probably don't want to overwrite those locked files.

Thanks again, Mike, for the original post and all the feedback!

Gabe

Friday, August 15, 2014 - 4:29:56 PM - Gabe Back To Top (34161)

Thanks again for the follow up!  I agree, that it would be rare that those files aren't in use but scares me to think I was that close to wiping them out.

I've always used the MOVE command to point to different mdf/ldf files because a script was given to me years ago.  How I discovered this "problem" is in using the Restore DB Wizard.  When I first used that (and didn't change the 'Restore As' file locations in the 'Options' menu) and got the error message that the main production mdf and ldf files were in use and can't be overwritten, my heart nearly stopped!  I couldn't believe that SQL wouldn't default to the non-production server's mdf/ldf files and started doing research and ultimately landed here.  I can honestly say, I've learned today!  It's funny, I've been doing this job for 9 years now and because of my limited exposure to larger production environments (more than one server and instance) I've had my eyes opened on a novice procedure.  Thanks for taking the time to explain and reply to my posts.  I now know to question "default" in everything I do.  ;-)

Gabe

 

Friday, August 15, 2014 - 12:44:02 PM - Mike Back To Top (34158)

Gabe - The only way I can think that the files for the production database wouldn't be locked would be if the database were detached from the instance and the files were left in place.  In that case, you could overwrite them with a restore command.  But if the database is online, I believe those file will remain locked by the SQL instance (database engine service), even if the database is not is actively in use.

In your situation, you're probably better off being overly cautious and continuing to use the MOVE clause.  This tip was merely intended to show that the MOVE command can be optional in certain situations.

Friday, August 15, 2014 - 10:44:59 AM - Gabe Back To Top (34152)

Mike,

Thanks for the reply.  Based on #3, I believe I'm on track in my thinking for my situation....

I'm a one-man-band in a one-server-office (one data server, one instance).  So, for me, when I do a backup of PRODUCTION_DB and then restore to NON_PRODUCTION_DB, if I don't use MOVE, it's by default trying to overwrite the production MDF and LDF files.  Is that a true statement?

If that's true, I understand that I'll get the message you've posted above, but let's assume those files aren't "being used by database PRODUCTION_DB"...this would suggest that even though I've entered the original command "RESTORE NON_PRODUCTION_DB", becasue I've left out the "MOVE" command, if PRODUCTION_DB's mdf and ldf files aren't locked, then I just wiped out the production db...correct?

Again, just trying to gain an understanding on this behavior so I don't destroy any mdf/ldf files in the future.

Gabe

Friday, August 15, 2014 - 10:12:03 AM - Mike Back To Top (34150)

Gabe - There are a couple of things here...

1) This example shows backing up one database and restoring it to another name on the same instance.  I would guess that most of us don't routinely restore backups to production instances, but rather restore a production backup to a non-production instance.

2) As the example shows, if you issue the RESTORE command for database A but the underlying file(s) are in use by database B, SQL Server will return an error similar to the following:

Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf' cannot be overwritten. It is being used by database 'AdventureWorks'.

3) If we restore a database with the same name and the same underlying files, and we use the REPLACE clause in our RESTORE command, the database will be overwritten because that is what we told SQL Server to do.

I hope that answers your questions.  If not, please feel free to contact me via email at [email protected].

-Mike

Thursday, August 14, 2014 - 1:37:54 PM - Gabe Back To Top (34138)

So if you don't use the MOVE command, then SQL Server will overwrite the SOURCE mdf and ldf files by default?  If SQL wasn't accessing them (they were somehow free to be overwritten), then we'd lose the original Adventure Works DB? If that's true, that's a potential catastrophe!  PLEASE correct me if I'm wrong.

 

In a real world scenario, your example above is synonymous with someone wanting to create a copy of a production DB to do some testing but if those main MDF and LDF files weren't locked, then they could potentially be destroying the live data...what if they were using a backup from 3 months ago...they just lost 3 months worth of data!!

Am I misunderstanding what's happening?  Would someone please clarify?!?

Wednesday, June 25, 2014 - 3:38:47 AM - Sahil Goyal Back To Top (32387)

Thanks for help.It save my time.

 

Thanks & regards,

 

Sahil Goyal

Friday, November 22, 2013 - 3:25:34 PM - Andrew Smith Back To Top (27580)

Facing the same scenario, but with 20 backup files in the backup set, and over 235 files comprising the database itself (due to table partitioning to unique filegroups), I chose to write a T-SQL script that generates the RESTORE WITH MOVE statement. Then you simply copy and paste the resulting statement (due to the previous limit on varchar strings) into a new query window and hit execute. Much easier and no typo's. First, create a table of the physical backup file names to restore. Then, simply do a RESTORE FILELISTONLY FROM DISK= '' on the first file in the backup set with the output going to a temp table. You must check the version first because the results returned from the RESTORE FILELISTONLY differs between SQL Server 2000, 2005 and 2008. This gives you the LogicalName and PhysicalName of all the database files. Then update the temp table by reading the sys.sysfiles view and updating the PhysicalName and by matching on the LogicalName. This is well worth it and I often use it on small restores with move just to avoide typo's.

Happy coding!

Regards,














get free sql tips
agree to terms