Restore Multiple SQL Server Databases Backup

Posted 25 November 2015 12:00 AM by Benjamin Moles, Senior .Net Developer @ ClearPeople

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

            
        

Share:

Add your comment

 
 

 

Archive

Tagcloud

Digital Transformation employee engagement staff satisfaction productivity Microsoft Teams Office 365 Yammer cms content management system agile GDPR Microsoft Graph collaboration Microsoft sharepoint 2016 upgrade migration SharePoint Online 2016 Tech Trends Digital Disruption Context marketing marketing SharePoint 2010 SharePoint 2013 TFS Git security kentico Analytics intranet jquery QA Quality Assurance testing digital workspace content management websites Sitecore sitecore marketplace sitecore module cloud Microsoft Cloud Storage digital strategy technical consulting sitecore modules Experience database Sitecore 7 Sitecore 8 support account management customer experience Data Storage windows azure cms integration front end front end development prototype Cloud Storage StorSimple Front-end Development Layout SharePoint 2013 colour palette UI design website design log viewer sitecore cms website Azure big data business-critical sharepoint accessibility android apple chrome clear people clearpeople debug emulator ios mobile testing opera resize adobe desktop flash ie10 internet explorer 10 metro windows 8 bcsp SharePoint Advanced System Reporter reporting framework ControlMode form control master page placeholder publishing console SharePoint 2007 SharePoint error search search results search values software testing testing scenario audit content information architecture retention schedules PowerShell QuickLaunch scripts SharePoint server 2010 business solutions metalogix replication replicator storagepoint stena technet UK Technet picture library slideshow web part RTM released to manufacturing caml caml query MOSS 2007 query infopath