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
