Blog Post

Tip: RESTORE FILELISTONLY / WITH MOVE

,

As we’ve seen in recent DBARant-able tales, not everyone is completely familiar with methods of restoring SQL backup files to new filepaths. The answer, in short, is: RESTORE FILELISTONLY/ RESTORE WITH MOVE.

Let’s say that you backed up the database ImportantDB from your production server, and you want to restore it to a test server. The only problem is, the data and log files on prod reside on the D: and E: drives , while the test box doesn’t HAVE D: and E: drives…only F: and G: drives.  Here’s what you do:

  • Use RESTORE FILELISTONLY to get the logical names of the data files in the backup. This is especially useful when you’re working with an unfamiliar backup file.

Example:

RESTORE FILELISTONLY

FROM DISK = \\srv1\sql\ImportantDB.bak’

  • Use RESTORE WITH MOVE to move and/or rename database files to a new path.

Example:

RESTORE DATABASE ImportantDB

FROM DISK = \\srv1\sql\ImportantDB.bak’

WITH MOVE ‘ImportantDB’ TO ‘F:\SQL\ImportantDB.mdf’,

MOVE ‘ImportantDB_log’ TO ‘G:\SQL\ImportantDB_log.LDF’

As they say on TV, it’s just that easy

And 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 it’s overlooked.

Happy days,

Jen McCown

http://www.MidnightDBA.com/Jen

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating