Restore Database WITH MOVE – A Review

Content rating: Beginner/review material.

The most common database restore I do is the WITH MOVE format.  I get DB backups from friends, book or presenter sites, and restore them on my box to play with.  The RESTORE syntax WITH MOVE, from BOL, is:

RESTORE DATABASE TestDB

FROM DISK = 'C:\AdventureWorks.bak'

WITH MOVE 'AdventureWorks_Data' TO 'C:\testdb.mdf',

MOVE 'AdventureWorks_Log' TO 'C:\testdb.ldf'

GO

See, database backups store information about the database they came from…namely, the logical file names, the source file locations, and so on.  When I restore a backup on my box, I'm very likely going to restore to a different filepath – I want to save my files in a folder on C:\, not F:\ (I don't even HAVE an F drive).  

Let's take a look.  First, locate the backup file itself – in this case, Brent Ozar's Twittercache database – and get the full filepath (e.g., is it saved in "c:\temp"?).  Then you can run this in SSMS:

RESTORE FILELISTONLY FROM DISK='C:\temp\TwitterCache.bak'

That'll get you the backup information:

TwitterCache F:\MSSQL\DATA\TwitterCache.mdf D PRIMARY 134217728 35184372080640 1 0 0 6DC5C93F-811F-40D3-9C8F-56692E098701 0 0 57147392 512 1 NULL 26000000004400037 B9297268-D556-44FE-9940-13A7B8550289 0 1
TwitterCache_log F:\MSSQL\DATA\TwitterCache_log.ldf L NULL 201138176 2199023255552 2 0 0 B5C0DCCF-9EBA-4A62-8B8A-6B09844B953C 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1

All you really need are the LogicalNames. In this example, we get these logical names:

TwitterCache
TwitterCache_log

Now build your restore statement, using the Logical Names (in bold) and the filepath to your SQL data and log folder(s):

RESTORE DATABASE TwitterCache

FROM DISK='C:\temp\TwitterCache.bak'

WITH MOVE 'TwitterCache' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TwitterCache.mdf',

MOVE 'TwitterCache_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TwitterCache_log.ldf' 

The easiest part to mess up is the comma between the MOVE statements, so don't miss it.

The end.
-Jen McCown
http://www.MidnightDBA.com

1 thought on “Restore Database WITH MOVE – A Review

  1. Pingback: SQL Awesomesauce » Blog Archive » Tip: RESTORE WITH FILELISTONLY / WITH MOVE

Comments are closed.