Restore all databases from a number of backup files

Home About me and Nucleus Partners Articles, tips etc. My blog Links Microsoft articles Forums Courses and training

Overview Sometimes you find yourself with a number of backup files and you want to do restore from each of these backup files. Enterprise Manager and 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 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-test 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. 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\' The code 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)