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?