|Restore all databases from a number of backup files|
About me and Nucleus
Articles, tips etc.
Courses and training
Sometimes you find yourself with a number of backup files and you want to do restore from each of these backup files. Management Studio can create backup history based on a backup file which can assist with restore from Enterprise Manager. This is done per backup files, though. And if you also need to specify a new path for the database files, you are in for going through a lot of dialogs.
Acknowledgements and versions of SQL Server
Below stored procedure reads the contents of a number of backup files in a directory and based on that generates RESTORE DATABASE commands. The outline of the procedure is:
This is the name of the directory where the backup files are stored.
This is the name of the directory where the databases' data files are to be created.
This is the name of the directory where the databases' log files are to be created.
Note that the procedure doesn't execute the RESTORE commands; it only outputs them to the result window so you can go through them before pasting them to the query window and executing them.
Only one backup in each backup file.
Only database backups in the files.
Only one mdf and one ldf file per database.
SQL Server 2000 or 2005 (Andreas Moe's version)
SQL Server 2005 with support for full-text (Jacky van Hogen's version)
SQL Server 2008 (Roberto Santoro's version)
Dan Guzman's proc for 2008 with support for many database files
SQL Server 2012 (Alexey Chirkunov's version)
SQL Server 2014 (Chris Burton's version)
SQL Server 2016 (My modifications. Thanks to Dirk, Johan Ryder Berntsen and David Moore for pointers and tips.)
Based on above including option to execute the SQL and to set in single user. Thanks to Henrik Staun Poulsen