How to restore Databases from backups files automatically in SQL Server - SQL Server: Tips and Tricks - 2 (2017)

SQL Server: Tips and Tricks - 2 (2017)

How to restore Databases from backups files automatically in SQL Server

Scenario:

Let's say that you are working as SQL Server DBA and you tons of backups files sitting in one of the folder, you need to restore all those backups files to newly created DEV, QA or UAT server. If you will start restoring one after one manually, it will take you forever. You are looking for some scripts those can restore these full backups files to newly created server.

Solution:
You will be able to use this type of script to refresh databases on regular basis such as monthly or on demand from Production to lower environments.

I have tested this script on SQL Server 2016. You might get some error in case you are using old versions or latest versions as sometime Microsoft change the number of columns returned by RESTORE FILELISTONLY.


Check the columns list and update the definition of temp tables according to your version, rest of the script will be same.

I did write same type of script before but I was considering the databases will have only mdf and ldf. The below script can handle database with NDF as well and overwrite existing DB if exists.

--Provide the folder path where backups are present

DECLARE @BackupFolder VARCHAR(128)

SET @BackupFolder='E:\Backup\'

--Provide the data and log files paths

--where you would like to restore databases

Declare @DataFilesPath VARCHAR(128)

Declare @LogFilesPath VARCHAR(128)

SET @DataFilesPath='D:\Data\'

SET @LogFilesPath='D:\Logs\'

--Get Backup files informaiton in temp table

IF OBJECT_ID('tempdb..#BackupFileList') IS NOT NULL

DROP TABLE #BackupFileList

--Drop temp table if exits

IF OBJECT_ID('tempdb..#RESTOREFILELISTONLY') IS NOT NULL

DROP TABLE #RESTOREFILELISTONLY

IF OBJECT_ID('tempdb..#RestoreHeaderOnly') IS NOT NULL

DROP TABLE #RestoreHeaderOnly

CREATE TABLE #BackupFileList (

Id int identity(1,1),

BackupFile nvarchar(255),

Depth smallint,

FileFlag bit)

--Select * From #BackupFileList

--Store Backup information

Create Table #RestoreHeaderOnly(

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 ,--2008 has till here

containment tinyint not NULL )--2012 has till here

--KeyAlgorithm nvarchar(32),--Add below if using sql 2014,2016

--EncryptorThumbprint varbinary(20) ,

--EncryptorType nvarchar(32))

--Select * From #RESTOREFILELISTONLY

--Create Temp Table for DB files

--Select * From #RESTOREFILELISTONLY

--Create Temp Table for DB files

Create table #RESTOREFILELISTONLY(

LogicalName NVARCHAR(128),

PhysicalName NVARCHAR(260),

Type CHAR(1),

FileGroupName NVARCHAR(128),

Size numeric(20,0),

MaxSize numeric(20,0),

Field bigint,

CreateLSN numeric(25,0),

DropLSN numeric(25,0),

UniqueId uniqueidentifier,

ReadonlyLSN numeric(25,0),

ReadWriteLSN numeric(25,0),

BackupSizeInBytes BigInt,

SourceBlockSize Int,

FileGroupId int,

LogGroupGUID uniqueidentifier,

DifferentialBaseLSN numeric(25,0),

DifferentialBaseGUID uniqueidentifier,

IsReadOnly bit,

IsPresent bit,

TDEThumprint varbinary(32)) --2012 is till here

--SnapshotURL nvarchar(360))

--Save backup files into temp table

INSERT INTO #BackupFileList (BackupFile,Depth,FileFlag)

EXEC xp_dirtree @BackupFolder, 10, 1

--Select * FROM #BackupFileList

--Use Cursor to loop throught backups files and restore

Declare @BackupFile VARCHAR(500)

DECLARE Cur CURSOR FOR

SELECT BackupFile from #BackupFileList

where fileflag=1

OPEN Cur

FETCH Next FROM Cur INTO @BackupFile

WHILE @@FETCH_STATUS = 0

BEGIN

Truncate table #RESTOREFILELISTONLY

--Insert data from RESTORE FILELISTONLY

insert into #RESTOREFILELISTONLY

EXEC('RESTORE FILELISTONLY FROM DISK = '''+@BackupFolder+@BackupFile+'''')

insert into #RestoreHeaderOnly

EXEC('RESTORE HEADERONLY FROM DISK = '''+@BackupFolder+@BackupFile+'''')

--Select * From #RESTOREFILELISTONLY

--Select * From #RestoreHeaderOnly

Declare @FilesNumber varchar(10)=Null

Declare @LogFileName NVARCHAR(128)=NULL

Declare @DataFileName NVARCHAr(128)=NULL

Declare @DBName NVARCHAR(128)=NULL

--We are considering we have a log and single data file

SET @LogFileName=(Select logicalName from #RESTOREFILELISTONLY where type='L')

SET @DataFileName=(Select logicalName from #RESTOREFILELISTONLY where type='D' and FileGroupName='PRIMARY')

SET @DBName=(Select top 1 DatabaseName from #RestoreHeaderOnly)

SET @FilesNumber=(Select count(*) From #RestoreHeaderOnly)

--Prepare Restore Statement

Declare @SQLRestore NVARCHAR(MAX)

SET @SQLRestore='RESTORE DATABASE "' +@DBName+'"

FROM DISK='''+@BackupFolder+@BackupFile+'''

WITH File='+@FilesNumber+',REPLACE,

MOVE '''+@DataFileName+''' TO '''+@DataFilesPath+@DataFileName+'.mdf'',

MOVE '''+@LogFileName+''' TO '''+@LogFilesPath+@LogFileName+'.ldf'''

--Execute SQL to Restore DBs

Print @SQLRestore

EXEC(@SQLRestore)

Truncate table #RestoreHeaderOnly

FETCH Next FROM Cur INTO @BackupFile

END

CLOSE Cur

DEALLOCATE Cur