X
Closing this message and/or accessing our website tells us you are happy to receive all cookies on the ClearPeople website.
However, if you would like to, you can change your cookies settings at any time.
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. 

 

Prerequisites 

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)

 

Configuration

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

VARIABLE NAME

USAGE

@BackupFolderPath

Location where all backup files can be accessed.

@DataFolderPath

Location where the databases’ data file must be restored.

@LogFolderPath

Location where the databases’ log file will be restored.

@RenameInternalFileNames

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

@Databases

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.

 

Script

            
            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)
            VALUES
                ('Test_QA_MainDb_20151021_142724.BAK', 'Test_UAT_MainDb')
                ,('Test_QA_Analytics_20151021_142801.BAK', NULL) -- Sample where original name will be used
                --,('', '')
            ;
            -- DATABASE SPECIFIC SETTINGS
            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   
            BEGIN   

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

                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;
                END

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

            
        

Author bio

Benjamin Moles
Benjamin Moles
Sitecore Developer
I'm a passionate Sitecore developer. I've worked with a broad variety of technologies and I am currently contributing to the success of ClearPeople's Sitecore projects. Beside IT stuff, I love to spend time with family and play with remote-controlled helicopters.

Comments


comments powered by Disqus

Related Articles

Sign up to our Newsletter

Every now and then, we'd like to send you information that delivers, develops and promotes our products and services that are relevant to you. Submitting your details tells us that you're OK with this and you also agree to our Privacy & Cookies policy. You can, of course, opt out of these communications at any time.