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?
Best Answer
Carlo,
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