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

7 thoughts on “Tip: RESTORE FILELISTONLY / WITH MOVE

  1. Jonathan

    Slightly outside this topic but related if people are looking to put this in place. Whan you are doing the backup make sure to use BACKUP DATABASE ImportantDB TO DISK N’C:\somelocation.bak’ WITH COPY_ONLY so that the backup chain is maintained.

  2. Pingback: Tweets that mention SQL Awesomesauce » Blog Archive » Tip: RESTORE WITH FILELISTONLY / WITH MOVE -- Topsy.com

  3. Pingback: From the Vault: Tip: RESTORE FILELISTONLY / WITH MOVE | The MidnightDBA Star-Times

  4. Robert Plata

    Where is the ” RESTORE WITH MOVE” command executed, on the production server or the test server? I am assuming the test server, but this post does not explain that. I want to be absolutely certain of where to execute it.

    Thanks

    1. Jen McCown Post author

      You always run a restore statement on the sql server instance you would like to restore the database to. In this example, we want to restore our database to the test server, so we run the RESTORE command on the test server.

  5. Robert Plata

    Thanks, Jen. I am a mainframe DB2 dino spreading my wings with SQL Server. This is very useful information.

Comments are closed.