Using the GUI to do this for more than a few databases at a time can be very cumbersome so I started on a script to alleviate some of this.
My goals for this script are:
- Able to quickly generate restore scripts for multiple databases
- Automate regular DR testing
You simply point the script at your backup directory and it will give you the scripts to run to restore your databases (Fulls, Diffs and Logs) to the most recent backup.
The script uses the undocumented master.sys.xp_dirtree to get the files from disk without the need to use xp_cmdshell.
The script is still in its infancy and has a long way to go but in its current form it will print the restore scripts to the screen.
Updated script - now works with SQL Server versions up to SQL Server 2019
Comments, suggestions and improvements are welcome.
Google Drive Link
Database Restore generator v0.4.sql
Comments, suggestions and improvements are welcome.
Google Drive Link
Database Restore generator v0.4.sql
Thank you so much for this great restore script. I had 280 databases Ola backups in a folder . With out your script I wouldn't be able to get it done in an hour. I appreciate it very much.
ReplyDeleteOn newer versions of SQL, adding a new column to DBRestorefilelist may be necessary. I used "SnapshotUrl varchar(max)" . That might be the wrong schema, but it was close enough to work.
ReplyDeleteJust what i needed.
ReplyDeleteAs mentioned above DBRestorefilelist needs another column for SQL Server 14.0 (2017).
Support has been added up to SQL 2019
ReplyDelete