MS SQL backup and restore commands (utilizing with move)

While I usually like to detach, copy/rename, then reattach development databases, for online databases I often need to refer to these SQL commands:

BACKUP DATABASE MY_DATABASE TO DISK=‘C:\SQLBACKUPS\MY_DATABASE_BACKUP20111117a.BACKUP’

RESTORE FILELISTONLY FROM DISK=‘C:\SQLBACKUPS\MY_DATABASE_BACKUP20111117a.BACKUP’

RESTORE DATABASE MY_DATABASE_NEW FROM DISK=‘C:\SQLBACKUPS\MY_DATABASE_BACKUP20111117a.BACKUP’
WITH
  MOVE ‘MY_DATABASE_Data’ TO ‘C:\PRODUCTION_DATA\MY_DATABASE.MDF’,
  MOVE ‘MY_DATABASE_Log’ TO ‘C:\PRODUCTION_DATA\MY_DATABASE.LDF’

Leave a Reply

Your email address will not be published. Required fields are marked *