More often than not, we get into a situation where we need to restore a large number of database backups, which are available in a backup directory. For example, when we are performing a restore drill or a refresh of the test server or a database server migration etc. Moreover, sometime we need to do this on a routine basis, like daily or weekly.
In my previous company, we used to have a SQL Server instance that was used for reporting purpose. This instance hosted a copy of all the user databases (more than 20) on the production server. The user databases on this instance were refreshed overnight by restoring the latest production database backups. Since these databases had to be refreshed every night, we had to find a way to automate this process.
To achieve this, I wrote this stored procedure (under the guidance of my mentor). This procedure takes the backup directory as a parameter and restores all the backup files available in that directory on the same instance where the procedure is being executed. The procedure is attached with this article and you can download it from there.
Let’s see how this works. In order to restore the databases automatically, the procedure should be able to do following things:
- Read the backup file names from the backup directory
- Get the database name from the backup file
- Retrieve the logical file names inside the backup file
- Prepare the backup command & execute it
Let's examine each of these in detail.
Read the backup file names from the backup directory
To do this I have used xp_cmdshell, which then executes DOS command “DIR” to get the backup file names. I understand that it can be a problem for many people to enable xp_cmdshell. If you want to use a CLR procedure to read the file names from directory, you can alter the procedure with your code.
Here I am using /B and /OD options with DIR command. /B provides the result in bare format i.e. without any summary or information. /OD sorts the result by modified datetime.
SET @GetFileListCMD = 'DIR '+@BackupFilesDirectory+'\*.'+@BackupFileExtension+' /B /OD'
IF OBJECT_ID('tempdb..#BackupFiles') IS NOT NULL
BEGIN
DROP TABLE #BackupFiles
END
CREATE TABLE #BackupFiles
(
FileID INT IDENTITY(1,1),
[FileName] VARCHAR(1000)
)
INSERT INTO #BackupFiles ([FileName])
EXEC MASTER..xp_cmdshell @GetFileListCMDGet the database name from the backup file
We can get the original database name for which the backup was taken by executing the RESTORE HEADERONLY command.
RESTORE HEADERONLY FROM DISK = 'D:\SQLTestLab\Backups\AdventureWorks_FULL_2014_04_03_18_11_23.BAK'
Retrieve the logical file names inside the backup file
We need the logical file names so that we can move them to the desired data file & log file directories when restoring the backup. We can get this information by executing RESTORE FILELISTONLY command.
RESTORE FILELISTONLY FROM DISK = 'D:\SQLTestLab\Backups\AdventureWorks_FULL_2014_04_03_18_11_23.BAK'
Prepare the backup command & execute it
Since we have all the information we need to restore the database, in this step our task is to prepare the restore command for each backup and then execute it or print it (whatever we like). Here, I have used a cursor which loops through the backup files and it prepares & executes (or prints) the restore commands for each backup file. I understand cursors are generally not good; however I think that they are fine for the purpose here.
Input Parameters
This stored procedure takes following input parameters. These parameters let you configure and control various things. For example, they allow you to provide the data file directory path where you want to put the data files of the restored bases and whether you want to just print the restore commands or you want to execute them.
- @BackupFilesDirectory - This is where you specify the directory path where the backup files are stored.
- @NewDataFilesDirectory - Directory path where the data files will be located after restore.
- @NewLogFilesDirectory - Directory path where the log files will be located after restore.
- @NewOtherFilesDirectory - I couldn’t think any other better name for this parameter. This is where you put any extra file other than data & log files such as Full Text Catalog files. I usually put these files in the same directory as my log files directory.
- @BackupFileExtension - Extension for backup files without dot (.) such as “bak” for native SQL Server backup files (you can specify * for all files).
- @ReplaceExistingDatabases - Setting this to 1 will force replace any existing database.
- @IncludeSystemDatabases - Setting this to 1 will generate the restore commands for system database as well.
- @AutoExecute - It can be set to 0 OR 1. 0 will only print the restore commands for backup files whereas 1 will execute them as well along with printing.
This is complete stored procedure script:
/********************************************************************************************************************
-----------> Script to restore all backup files inside a directory to a SQL Server instance. <-----------
Author : Sujeet Pratap Singh
Procedure : SPS_RestoreBackup
Description : This stored procedure takes the directory location of sql server database backup files and restores all the database
backup files from given location.
Parameters : @BackupFilesDirectory = Directory path where all the backup files are stored.
@NewDataFilesDirectory = Directory path to store the Data files of restored databases.
@NewLogFilesDirectory = Directory path to store the Log files of restored databases.
@NewOtherFilesDirectory = Directory path to store the other files (such as Full Text Search files etc.) of restored databases.
@BackupFileExtension = Extension for backup files without dot (.) such as bak for native SQL Server backup files ( you can specify * for all files)
@ReplaceExistingDatabases = Setting this to 1 will replace any existing database.
@IncludeSystemDatabases = Setting this to 1 will generate the restore commands for system database as well
@AutoExecute = It can be set to 0 OR 1. When set to 0 (default), it will only print the restore commands for backup files where as setting it to 1 will EXECUTE the command automatically along with printing.
Execution : EXECUTE SPS_RestoreDatabases @BackupFilesDirectory = 'D:\SQLTestLab\Backups',
@NewDataFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\DataFiles',
@NewLogFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\LogFiles',
@NewOtherFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\LogFiles',
@BackupFileExtension = 'bak',
@ReplaceExistingDatabases = 0,
@IncludeSystemDatabases = 0,
@AutoExecute = 0
Last modified : 06-April-2014 (Modified for SQL Server 2012 & above.)
********************************************************************************************************************/
IF OBJECT_ID('SPS_RestoreDatabases') IS NOT NULL
BEGIN
DROP PROCEDURE SPS_RestoreDatabases;
END
GO
CREATE PROCEDURE [dbo].[SPS_RestoreDatabases]
(
@BackupFilesDirectory VARCHAR(500),
@NewDataFilesDirectory VARCHAR(500),
@NewLogFilesDirectory VARCHAR(500),
@NewOtherFilesDirectory VARCHAR(500),
@BackupFileExtension VARCHAR(20) = 'bak',
@ReplaceExistingDatabases BIT = 0,
@IncludeSystemDatabases BIT = 0,
@AutoExecute BIT = 0
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @FileName VARCHAR(1000)
DECLARE @DataFileName VARCHAR(1000)
DECLARE @LogFileName VARCHAR(1000)
DECLARE @FTSFileName VARCHAR(1000)
DECLARE @DataFileMoveStatements VARCHAR(1000)
DECLARE @LogFileMoveStatements VARCHAR(1000)
DECLARE @OtherFileMoveStatements VARCHAR(1000)
DECLARE @GetFileListCMD VARCHAR(1000)
DECLARE @DatabaseName VARCHAR(1000)
DECLARE @FileID INT
DECLARE @DatabaseRestoreCommand VARCHAR(1000)
DECLARE @DataFileCounter INT
DECLARE @LogFileCounter INT
DECLARE @OtherFileCounter INT
DECLARE @RestrictDatabaseAccess VARCHAR(1000)
DECLARE @Ext VARCHAR(11)
IF (@BackupFilesDirectory IS NULL)
BEGIN
RAISERROR ( 'Provide a valid backup directory path.',16,1)
END
ELSE IF (@NewDataFilesDirectory IS NULL)
BEGIN
RAISERROR ( 'Provide a valid path for @NewDataFilesDirectory.',16,1)
END
ELSE IF (@NewLogFilesDirectory IS NULL)
BEGIN
RAISERROR ( 'Provide a valid path for @NewLogFilesDirectory.',16,1)
END
ELSE IF (@NewOtherFilesDirectory IS NULL)
BEGIN
RAISERROR ( 'Provide a valid path for @NewOtherFilesDirectory.',16,1)
END
ELSE IF (@IncludeSystemDatabases = 1 AND @AutoExecute = 1)
BEGIN
RAISERROR ( 'AutoExecute cannot be used with system databases. Set @AutoExecute = 0 to print the restore commands for system databases & then restore them manually OR set @IncludeSystemDatabases = 0 to exclude system databases. ',16,1)
END
ELSE
BEGIN
--======================= Storing backup file names
SET @GetFileListCMD = 'DIR '+@BackupFilesDirectory+'\*.'+@BackupFileExtension+' /B /OD'
IF OBJECT_ID('tempdb..#BackupFiles') IS NOT NULL
BEGIN
DROP TABLE #BackupFiles
END
CREATE TABLE #BackupFiles
(
FileID INT IDENTITY(1,1),
[FileName] VARCHAR(1000)
)
INSERT INTO #BackupFiles ([FileName])
EXEC MASTER..xp_cmdshell @GetFileListCMD
DELETE FROM #BackupFiles WHERE [FileName] IS NULL OR [FileName] LIKE '%File Not Found%'
--======================= Temporary table to store backup header information
IF OBJECT_ID('tempdb..#RestoreHeaderResults') IS NOT NULL
BEGIN
DROP TABLE #RestoreHeaderResults
END
CREATE TABLE #RestoreHeaderResults
(
BackupName VARCHAR(250),
BackupDescription VARCHAR(500),
[BackupType] INT,
ExpirationDate DATETIME,
Compressed VARCHAR(2),
Position INT,
DeviceType INT,
UserName VARCHAR(250),
ServerName VARCHAR(250),
DatabaseName VARCHAR(250),
DatabaseVersion VARCHAR(250),
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 INT,
[CodePage] Numeric(18,2),
UnicodeLocaleId INT,
UnicodeComparisonStyle Numeric(18,2),
CompatibilityLevel VARCHAR(10),
SoftwareVendorId VARCHAR(10),
SoftwareVersionMajor Varchar (250),
SoftwareVersionMinor Varchar (250),
SoftwareVersionBuild Varchar (250),
MachineName Varchar (250),
Flags Varchar (250),
BindingID Varchar (250),
RecoveryForkID Varchar (250),
Collation Varchar (250),
FamilyGUID Varchar (250),
HasBulkLoggedData Varchar (250),
IsSnapshot Varchar (250),
IsReadOnly Varchar (250),
IsSingleUser Varchar (250),
HasBackupChecksums Varchar (250),
IsDamaged Varchar (250),
BeginsLogChain Varchar (250),
HasIncompleteMetaData Varchar (250),
IsForceOffline Varchar (250),
IsCopyOnly Varchar (250),
FirstRecoveryForkID Varchar (250),
ForkPointLSN Varchar (250),
RecoveryModel Varchar (250),
DifferentialBaseLSN Varchar (250),
DifferentialBaseGUID Varchar (250),
BackupTypeDescription Varchar (250),
BackupSetGUID Varchar (250),
CompressedBackupSize Numeric(18,2),
Containment TINYINT
)
--======================= Temporary table to store details of database files from backup file
IF OBJECT_ID('tempdb..#RestoreFileListResults') IS NOT NULL
BEGIN
DROP TABLE #RestoreFileListResults
END
CREATE TABLE #RestoreFileListResults
(
RowID INT IDENTITY(1,1),
LogicalName VARCHAR(250),
PhysicalName VARCHAR(500),
[Type] VARCHAR(2),
FileGroupName VARCHAR(100),
Size VARCHAR(100),
MaxSize VARCHAR(100),
FileId INT,
CreateLSN VARCHAR(250),
DropLSN VARCHAR(250),
UniqueId VARCHAR(250),
ReadOnlyLSN VARCHAR(250),
ReadWriteLSN VARCHAR(250),
BackupSizeInBytes VARCHAR(250),
SourceBlockSize INT,
FileGroupId INT,
FileGroupGUID VARCHAR(250),
DifferentialBaseLSN VARCHAR(250),
DifferentialBaseGUID VARCHAR(250),
IsReadOnly VARCHAR(2),
IsPresent VARCHAR(2),
TDEThumbPrint VARCHAR(250)
)
--======================= Getting complete path of backup file
DECLARE @BackupFileName VARCHAR(100)
DECLARE @CompleteBackupFilePath VARCHAR(500)
--======================= Looping through the backup files to generate database restore commands
DECLARE BackupFilesList CURSOR FAST_FORWARD FOR SELECT [FileName] FROM #BackupFiles ORDER BY [FileName],[FileID]
OPEN BackupFilesList
FETCH NEXT FROM BackupFilesList INTO @BackupFileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CompleteBackupFilePath = @BackupFilesDirectory + '\' + @BackupFileName
--======================= Storing backup header information in temporary table
TRUNCATE TABLE #RestoreHeaderResults;
INSERT INTO #RestoreHeaderResults ( BackupName,
BackupDescription,
BackupType,
ExpirationDate,
Compressed,
Position,
DeviceType,
UserName,
ServerName,
DatabaseName,
DatabaseVersion,
DatabaseCreationDate,
BackupSize,
FirstLSN,
LastLSN,
CheckpointLSN,
DatabaseBackupLSN,
BackupStartDate,
BackupFinishDate,
SortOrder,
[CodePage],
UnicodeLocaleId,
UnicodeComparisonStyle,
CompatibilityLevel,
SoftwareVendorId,
SoftwareVersionMajor,
SoftwareVersionMinor,
SoftwareVersionBuild,
MachineName,
Flags,
BindingID,
RecoveryForkID,
Collation,
FamilyGUID,
HasBulkLoggedData,
IsSnapshot,
IsReadOnly,
IsSingleUser,
HasBackupChecksums,
IsDamaged,
BeginsLogChain,
HasIncompleteMetaData,
IsForceOffline,
IsCopyOnly,
FirstRecoveryForkID,
ForkPointLSN,
RecoveryModel,
DifferentialBaseLSN,
DifferentialBaseGUID,
BackupTypeDescription,
BackupSetGUID,
CompressedBackupSize,
Containment)
EXEC('RESTORE HEADERONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')
--======================= Storing internal details of data & log files on the backup file
TRUNCATE TABLE #RestoreFileListResults;
INSERT INTO #RestoreFileListResults (
LogicalName,
PhysicalName,
[Type],
FileGroupName,
Size,
MaxSize,
FileId,
CreateLSN,
DropLSN,
UniqueId,
ReadOnlyLSN,
ReadWriteLSN,
BackupSizeInBytes,
SourceBlockSize,
FileGroupId,
FileGroupGUID,
DifferentialBaseLSN,
DifferentialBaseGUID,
IsReadOnly,
IsPresent,
TDEThumbPrint)
EXEC('RESTORE FILELISTONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')
--======================= Getting the name of the new database to be restored
SELECT @DatabaseName = DatabaseName FROM #RestoreHeaderResults
SET @DataFileMoveStatements = ''
SET @LogFileMoveStatements = ''
SET @OtherFileMoveStatements = ''
--======================= Generating statments to move data files
SELECT @DataFileMoveStatements = @DataFileMoveStatements + 'MOVE'+''''+LogicalName+''''+' TO '+''''+@NewDataFilesDirectory+'\'+ RIGHT(RFLR.PhysicalName, CHARINDEX('\', REVERSE(RFLR.PhysicalName))-1) +''','+CHAR(13)
FROM #RestoreFileListResults RFLR
WHERE RFLR.[Type] = 'D'
ORDER BY RFLR.FileId
--======================= Generating statments to move log files
SELECT @LogFileMoveStatements = @LogFileMoveStatements+'MOVE '+''''+LogicalName+''''+' TO '+''''+@NewLogFilesDirectory+'\'+ RIGHT(RFLR.PhysicalName, CHARINDEX('\', REVERSE(RFLR.PhysicalName))-1) +''','+CHAR(13)
FROM #RestoreFileListResults RFLR
WHERE RFLR.[Type] = 'L'
ORDER BY RFLR.FileId
--======================= Generating statments to move other files such as FileStream or FullTextCatalog files
SELECT @OtherFileMoveStatements = @OtherFileMoveStatements+'MOVE '+''''+LogicalName+''''+' TO '+''''+@NewOtherFilesDirectory+'\'+ RIGHT(RFLR.PhysicalName, CHARINDEX('\', REVERSE(RFLR.PhysicalName))-1) +''','+CHAR(13)
FROM #RestoreFileListResults RFLR
WHERE RFLR.[Type] NOT IN ('D','L')
ORDER BY RFLR.FileId
--======================= Preparing database restore commands
IF (@ReplaceExistingDatabases = 1)
BEGIN
SET @RestrictDatabaseAccess = ''
IF DB_ID(@DatabaseName) IS NOT NULL
BEGIN
SET @RestrictDatabaseAccess = 'ALTER DATABASE ['+ @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
END
SET @DatabaseRestoreCommand = 'RESTORE DATABASE ['+@DatabaseName+'] FROM DISK ='+''''+ @CompleteBackupFilePath+''''+CHAR(13)+'WITH '+CHAR(13)+@DataFileMoveStatements + @LogFileMoveStatements + @OtherFileMoveStatements + 'STATS = 1,REPLACE;'+CHAR(13)+''
SET @DatabaseRestoreCommand = @RestrictDatabaseAccess + CHAR(13) + CHAR(13) + @DatabaseRestoreCommand
END
ELSE
BEGIN
SET @DatabaseRestoreCommand = 'RESTORE DATABASE ['+@DatabaseName+'] FROM DISK ='+''''+ @CompleteBackupFilePath+''''+CHAR(13)+'WITH '+CHAR(13)+@DataFileMoveStatements + @LogFileMoveStatements + @OtherFileMoveStatements + 'STATS = 1;'+CHAR(13)+''
END
--======================= Preparing & printing database restore commands
IF (@AutoExecute = 0 AND @IncludeSystemDatabases = 1)
BEGIN
PRINT CHAR(13) + '--' + REPLICATE('=',40) + ' Restore Command For Database : ['+@DatabaseName+']' + CHAR(13)
PRINT @DatabaseRestoreCommand
PRINT CHAR(13) + '--' + REPLICATE('=',130)
END
IF (@AutoExecute = 0 AND @IncludeSystemDatabases = 0)
BEGIN
IF (UPPER(@DatabaseName) NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION') AND NOT EXISTS (SELECT * FROM sys.databases WHERE [name] = @DatabaseName AND is_distributor = 1))
BEGIN
PRINT CHAR(13) + '--' + REPLICATE('=',40) + ' Restore Command For Database : ['+@DatabaseName+']' + CHAR(13)
PRINT @DatabaseRestoreCommand
PRINT CHAR(13) + '--' + REPLICATE('=',130)
END
END
IF (@AutoExecute = 1 AND @IncludeSystemDatabases = 0)
BEGIN
IF (UPPER(@DatabaseName) NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION') AND NOT EXISTS (SELECT * FROM sys.databases WHERE [name] = @DatabaseName AND is_distributor = 1))
BEGIN
PRINT CHAR(13) + '--' + REPLICATE('=',40) + ' Executing Restore Command For Database : ['+@DatabaseName+']' + CHAR(13)
PRINT @DatabaseRestoreCommand
EXECUTE (@DatabaseRestoreCommand)
PRINT CHAR(13) + '--' + REPLICATE('=',130)
END
END
FETCH NEXT FROM BackupFilesList INTO @BackupFileName
END
CLOSE BackupFilesList
DEALLOCATE BackupFilesList
END
ENDNote: This procedure restores FULL backups only. It doesn’t support differential & log backups, as of now.
So, that's all for now, friends. Thanks a lot for your time 🙂
