Restore all databases from a number of backup files

Overview
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.


The code
Tim Cartwright did a couple of versions on GitHub that adapts automatically to the version. One that executes the RESTORE HEADERONLY command and another that allow you to get the info from RESTORE HEADERONLY into variables.
Based on below including option to execute the SQL and to set in single user. Thanks to Henrik Staun Poulsen
SQL Server 2022 (Manuel Blickle's version)
SQL Server 2016
(My modifications. Thanks to Dirk, Johan Ryder Berntsen and David Moore for pointers and tips.)
SQL Server 2014 (Chris Burton's version)
SQL Server 2012 (Alexey Chirkunov's version)
SQL Server 2008 (Roberto Santoro's version)
SQL Server 2005 with support for full-text (Jacky van Hogen's version)
SQL Server 2000 or 2005 (Andreas Moe's version)

Acknowledgements and versions of SQL Server

  • The procedure was originally written for 2000. Basically with a new version of SQL Server, you need to look at the output from RESTORE HEADERONLY and RESTORE FILELISTONLY to see if they return more columns than in the version that the procedure is written for.
  • Andreas Moe, http://braathe.no/, sent me a version that also work on 2005.
  • Jacky van Hogen sent me a version that supports full-text catalogs, including a parameter for where to create the full-text catalog files.
  • Roberto Santoro sent me a version adapted for 2008.
  • Dan Guzman has a proc for 2008 with support for many database files.
  • Alexey Chirkunov sent me a version adapted for 2012.
  • Chris Burton sent me a version that work for 2014. He also added REPLACE, amended the MOVE option to generate database file named based on the database name instead of the original file names and added delimiters for the database names.
  • Henrik Staun Poulsen adapted the 2016 version with option to execute the SQL and also option to set the database in single user mode.

Outline
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:

  • Use xp_dirtree to save all file names in a directory in a temp table.
  • For each file, EXEC RESTORE HEADERONLY into a temp table to get the database name from the backup file.
  • Use EXEC and RESTORE FILELISTONLY into a temp table so we can go through that and generate MOVE for each database file.
  • Print out the RESTORE commands.
Usage
@SourceDirBackupFiles nvarchar(200)
This is the name of the directory where the backup files are stored.

@DestDirDbFiles nvarchar(200)
This is the name of the directory where the databases' data files are to be created.

@DestDirLogFiles nvarchar(200)
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.

Limitations
Only one backup in each backup file.
Only database backups in the files.
Only one mdf and one ldf file per database.

Sample execution
EXEC sp_RestoreFromAllFilesInDirectory 'C:\Temp\', 'C:\SqlDataFiles\',  'D:\SqlLogFiles\'