ClearPeople logo

Restore Multiple SQL Server Databases Backup

25 November 2015

Our guide to restoring multiple SQL Server database Backups


Restoring SQL Server backups is a common task amongst developers and consultants. Often we need to restore collections of databases (backup files) in new environments. It is common to do it in order to create a demo environment or to replicate one environment onto another. 


We have created a small script that facilitates this task allowing us to restore many databases in one single step. I thought it would be useful to share with the wider developer community. 



Here's a list of the prerequisites required for the script to work:

  • Access to the backup files to be restored. The backup files must be located in a folder where SQL Server account can access
  • Each backup file must have only one database backup
  • The database within the backup file must have only two files:
    • Data file
    • Log file
  • The characters for the new database to be restored must be allowed for physical file names as we use the database name to name its physical files on the disk.

SQL Server Version

The script has been tested with SQL Server 2014 (12.0.4213.0)



The script provides the following variables that allow us to specify the required configuration:





Location where all backup files can be accessed.


Location where the databases’ data file must be restored.


Location where the databases’ log file will be restored.


Indicates whether the names used by SQL Server to identify each database file must be renamed with the new database name. 


List with the backup file name and the new name to use for each restored database. To use the original database name leave the “NewDbName” field NULL.

Internal Files' Name

When we create a new database, by default its files are internally identified with the database name followed by a suffix. For instance a new database called “QA_Data” would have two files named:
  • “QA_Data”: for the data *.mdf file
  • QA_Data _log” for the log *.ldf file.
If we back-up this database, the internal file names will be stored in the backup and will be restored in a new environment.
The script allows us to restore the QA backups into an UAT environment and keep consistency, not only in the physical database files but also in their internal identification. We can use the variable named @RenameInternalFileNames to disable this behaviour in those cases where we want to keep the original internal names.



            USE [master]
            -- GENERAL SETTINGS
            DECLARE @BackupFolderPath NVarChar(256);    -- Path for backup files  
            DECLARE @DataFolderPath NVarChar(256);  -- Path for data files  
            DECLARE @LogFolderPath NVarChar(256);       -- Path for log files 
            DECLARE @RenameInternalFileNames Bit;       -- Change identification of database files whithin the database?
            DECLARE @Databases TABLE (NewDbName NVarChar(256), BackupFileName NVarChar(256));
            SET @BackupFolderPath = 'C:\Backups\';
            SET @DataFolderPath = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\';
            SET @LogFolderPath = @DataFolderPath; -- Change it to save logs in different location.
            SET @RenameInternalFileNames = 1;   -- Set to 1 to keep consistency between DB name and file names
            INSERT INTO @Databases (BackupFileName, NewDbName)
                ('Test_QA_MainDb_20151021_142724.BAK', 'Test_UAT_MainDb')
                ,('Test_QA_Analytics_20151021_142801.BAK', NULL) -- Sample where original name will be used
                --,('', '')
            DECLARE @NewDbName NVarChar(256); -- database name  
            DECLARE @BackupFileName NVarChar(256);
            DECLARE @OldDataFileName NVarChar(256);
            DECLARE @OldLogFileName NVarChar(256);
            DECLARE @NewDataFileName NVarChar(256);
            DECLARE @NewLogFileName NVarChar(256);

            -- Auxiliar
            DECLARE @BackupFilePath NVarChar(MAX);
            DECLARE @DataFilePath NVarChar(MAX);
            DECLARE @LogFilePath NVarChar(MAX);
            DECLARE @Sql NVarChar(MAX);
            DECLARE @SavedDatabases TABLE(
                 BackupName nvarchar(128), BackupDescription nvarchar(255), BackupType smallint, ExpirationDate datetime
                , Compressed Bit, Position smallint, DeviceType tinyint, UserName nvarchar(128), ServerName nvarchar(128)
                , DatabaseName nvarchar(128), DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(20,0)
                , FirstLSN numeric(25,0), LastLSN numeric(25,0), CheckpointLSN numeric(25,0), DatabaseBackupLSN numeric(25,0)
                , BackupStartDate datetime, BackupFinishDate datetime, SortOrder smallint, [CodePage] smallint, UnicodeLocaleId int
                , UnicodeComparisonStyle int, CompatibilityLevel tinyint, SoftwareVendorId int, SoftwareVersionMajor int
                , SoftwareVersionMinor int, SoftwareVersionBuild int, MachineName nvarchar(128), Flags int, BindingID uniqueidentifier
                , RecoveryForkID uniqueidentifier, Collation nvarchar(128), FamilyGUID uniqueidentifier, HasBulkLoggedData bit
                , IsSnapshot bit, IsReadOnly bit, IsSingleUser bit, HasBackupChecksums bit, IsDamaged bit, BeginsLogChain bit
                , HasIncompleteMetaData bit, IsForceOffline bit, IsCopyOnly bit, FirstRecoveryForkID uniqueidentifier
                , ForkPointLSN numeric(25,0)NULL, RecoveryModel nvarchar(60), DifferentialBaseLSN  numeric(25,0)NULL
                , DifferentialBaseGUID uniqueidentifier, BackupTypeDescription nvarchar(60), BackupSetGUID uniqueidentifier NULL
                , CompressedBackupSize bigint, containment tinyint not NULL, KeyAlgorithm nvarchar(32)
                , EncryptorThumbprint varbinary(20), EncryptorType nvarchar(32)
            DECLARE @BackupDbFiles TABLE (
                LogicalName NVarChar(128), [PhysicalName] NVarChar(128), [Type] NVarChar, [FileGroupName] NVarChar(128)
                , [Size] NVarChar(128), [MaxSize] NVarChar(128), [FileId] NVarChar(128), [CreateLSN] NVarChar(128)
                , [DropLSN] NVarChar(128), [UniqueId] NVarChar(128), [ReadOnlyLSN] NVarChar(128), [ReadWriteLSN] NVarChar(128)
                , [BackupSizeInBytes] NVarChar(128), [SourceBlockSize] NVarChar(128), [FileGroupId] NVarChar(128)
                , [LogGroupGUID] NVarChar(128), [DifferentialBaseLSN] NVarChar(128), [DifferentialBaseGUID] NVarChar(128)
                , [IsReadOnly] NVarChar(128), [IsPresent] NVarChar(128), [TDEThumbprint] NVarChar(128)

            -- Iterate through all databases to restore
            DECLARE db_cursor CURSOR FOR  
            SELECT NewDbName, BackupFileName
            FROM @Databases;
            OPEN db_cursor;
            FETCH NEXT FROM db_cursor INTO @NewDbName, @BackupFileName;

            WHILE @@FETCH_STATUS = 0   

                IF (@NewDbName IS NULL) BEGIN
                    DELETE @SavedDatabases;
                   INSERT INTO @SavedDatabases
                    EXEC('RESTORE HEADERONLY FROM DISK=''' +@BackupFilePath+ '''');
                    SET @NewDbName=(SELECT DatabaseName FROM @SavedDatabases)

                SET @BackupFilePath = @BackupFolderPath + @BackupFileName;
                SET @DataFilePath = @DataFolderPath + @NewDbName + '.mdf';
                SET @LogFilePath = @LogFolderPath + @NewDbName + '.ldf';

                DELETE @BackupDbFiles;
                INSERT INTO @BackupDbFiles
                EXEC('RESTORE FILELISTONLY FROM DISK=''' +@BackupFilePath+ '''');

                SET @OldDataFileName=(SELECT LogicalName FROM @BackupDbFiles WHERE Type='D')
                SET @OldLogFileName=(SELECT LogicalName FROM @BackupDbFiles WHERE Type='L')
                SET @NewDataFileName = @NewDbName;
                SET @NewLogFileName = @NewDbName + '_log';

                -- Backup database
                RESTORE DATABASE @NewDbName 
                FROM  DISK = @BackupFilePath WITH  FILE = 1
                    ,  MOVE @OldDataFileName TO @DataFilePath
                    ,  MOVE @OldLogFileName TO @LogFilePath
                    ,  NOUNLOAD,  STATS = 5

                -- Rename SQL Server internal file names
                IF (@RenameInternalFileNames= 1) BEGIN
                    SET @Sql = N'ALTER DATABASE ['+@NewDbName+N'] MODIFY FILE (NAME=N'''+@OldDataFileName+N''', NEWNAME=N'''+@NewDataFileName+N''');';
                    EXEC sp_executesql @Sql;
                    SET @Sql = N'ALTER DATABASE ['+@NewDbName+N'] MODIFY FILE (NAME=N'''+@OldLogFileName+N''', NEWNAME=N'''+@NewLogFileName+N''');';
                    EXEC sp_executesql @Sql;

                FETCH NEXT FROM db_cursor INTO @NewDbName, @BackupFileName
            CLOSE db_cursor
            DEALLOCATE db_cursor


Author bio

Benjamin Moles

Benjamin Moles

My goal is to return happiness to clients' faces by getting their issues and questions solved. As a Technical Service Manager I provide the required resources to support clients' applications and infrastructure. I also enjoy playing with remote-controlled helicopters.

View all articles by this author