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
[...] yes, I realize that I wrote about this last year…but I’m gonna take the hit on this one. It’s useful, it’s important, and [...]