Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Automating Database Restores

By Sujeet Singh,

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:

  1. Read the backup file names from the backup directory
  2. Get the database name from the backup file
  3. Retrieve the logical file names inside the backup file
  4. 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 @GetFileListCMD

Get 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 

END

Note: 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 :-)

Resources:

SPS_RestoreDatabases.sql
Total article views: 5490 | Views in the last 30 days: 18
 
Related Articles
FORUM

Backup and Restore

i want to do database backup and restore.

ARTICLE

Identify the Backup used to Restore the Database

This article will help us identify the backup which was used to restore the database. This may seem ...

FORUM

Backup/restore

Backup/restore

FORUM

Restore from Backup with Replicated Database

Restore from Backup with Replicated Database

FORUM

database backup and restore in sql server 2005

database backup and restore using java

Tags
automation    
backup    
restore    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones