Home » Uncategorized » Currently Reading:

Restore Database WITH MOVE – A Review

December 3, 2009 Uncategorized 1 Comment

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

Currently there is "1 comment" on this Article:

  1. [...] 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 [...]

Comment on this Article:







MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

April 14: Oklahoma City SQL UG's 5th Anniversary
April 28-30: Powershell Summit in Bellevue, WA
May 10: SQL Saturday Houston
August 2: SQL Saturday Baton Rouge (planned)
August 22-23: SQL Saturday Oklahoma City Precon!

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/