Restore Multiple SQL Server Databases Backup

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:

Internal Files' Name

• “QA_Data”: for the data *.mdf file
• QA_Data _log” for the log *.ldf file.

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