question

Parthishri avatar image
Parthishri asked

Restore script using the backup file extension

Hi Guys, Can anyone help me in providing the SQL Script to restore my staging database using the production database copy. In my current environment my production database backups are being taken through SQL Maintenance plan. Hence the production database backup file name included with date format. So I need a script to restore my staging database using the latest backup file available on the production backup path. Production Server Name: X Staging Server Name : Y Production Database Name: AP Staging Database Name: AS
restore
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
The key is to use the same scripting format for the name of the file as you did to name it. Other than that, it's pretty straight-forward to restore to a new name. You just need to make sure you use WITH MOVE option to move the data files: RESTORE DATABASE [AS] --awful name since it's a reserve word FROM DISK = 'x:\backups\3_15_63_AP.BAK' WITH MOVE 'AP_Data' TO 'd:\data\AS_Data.mdf', MOVE 'AP_Log' TO 'e:\log\AS_Log.ldf', REPLACE, -- needed because you're restoring a different database on top of this one RECOVERY As far as scripting out the name goes, you can substitute what I have here as a string, '3_15_63_AP.BAK', with a variable that you build the same way you build your backup name. For more on RESTORE, always reference [the Books Online][1]. [1]: http://technet.microsoft.com/en-us/library/ms186858.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.