We are doing a major upgrade this weekend, so like any good DBA, I have planned for a full backup the night before and needed the ability to quickly restore if it goes sideways and needs to roll back.
The catch is that there are close to 300 identical databases involved.
This is easy enough to do if you know where info is stored in MSDB related to the last backup.
I’ll just leave the code I used right here for your amusement:
Use MSDB; go --generate the list Select max(backup_start_date) as Backup_Date, database_name, Max(physical_device_name) as physical_device_name Into #lastFullBackup From msdb..backupset bs join msdb..backupmediafamily bmf on bs.media_set_id = bmf.media_set_id Where 1=1 and backup_start_date > getdate() -1 and type = 'D' Group By database_name Order By database_name --Create the restore commands Select 'restore database ['+database_name+'] From disk = ''' +physical_device_name+''' With replace, recovery' From #lastFullBackup Where database_name like '%MyDB%[0-9]' -- database1, database2, etc. --Cleanup Drop table #lastFullBackup
There are probably much easier or prettier ways to do this, but I’m only 2 cups of coffee in thus far, and one of them got cold…
If any of the above doesn’t make sense, please drop a comment. It works on my system and should be valid for SQL 2000 – 2016…the system tables referenced have not changed.
Have a bright, shiny day full of puppies or muscle cars….whichever you prefer!