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