1

I want in my maintenance plan every night to copy the production database to a backup, and reload the newly-created backup into a test database. By reading here:

Restore database - maintenance plan task

I see that it is possible to do it using first the Back Up Database Task and then the Execute T-SQL Statement Task. The problem is, the backup file has every time a different name, like

SIAM_backup_2014_08_14_221501_2611766.bak

If I don't find a better solution, I will have to save the database backup always on the same filename, overwriting it every time. Is it possible to pass a parameter between the two tasks, with the name of the backup file, or is there a T-SQL statement that means "Reload the latest saved full backup", or do you have other ideas?

|improve this question
2

Carlo,

do you have other ideas?

You jus took a backup, so when you write the execute SQL Statement part of your job reference the MSDB backup history information tables to pull out the latest full and any other diffs/logs/etc that you would like to apply. The data can be found in msdb under the dbo schema and will give you the file names, etc, so there will be no need to pass anything and you can make it work should you want to do it from the last full or from the last full with the rest of the backup chain.

http://msdn.microsoft.com/en-us/library/ms188062.aspx

|improve this answer
  • Thanks. I can get the name of the latest backup like this: select top 1 physical_device_name from msdb..backupmediafamily where physical_device_name like '%.bak' order by media_set_id desc So I tried this, but it fails: RESTORE DATABASE SIAMTEST2 FROM DISK = ( select top 1 physical_device_name from msdb..backupmediafamily where physical_device_name like '%.bak' order by media_set_id desc ) WITH MOVE 'SIAM' TO 'H:\data\SIAMTEST2.mdf', MOVE 'SIAM_log' TO 'I:\data\SIAMTEST2_log.ldf', REPLACE Do I have a syntax error? – carlo.borreo Aug 15 '14 at 14:49
  • If what I am trying to do is impossible (fetch the filename of latest backup, and use it for a restore, in a single query) then is it possible to use two steps in the maintenance plan, and pass the filename from one to the other? – carlo.borreo Aug 15 '14 at 14:54
  • Yes, you have an error in your query in the fact that you can't use a subquery in a restore or backup command. You can however, use a variable. For example: RESTORE DATABASE ... FROM DISK = @BackupFile and get those variables populated from other queries before you run the restore command.Here is an example: mssqltips.com/sqlservertip/1601/… – Sean Gallardy Aug 15 '14 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.