How to automate database backup and restore using T-SQL

Scenario

We may have scenario to restore a development or test database or just create another copy of the database. Restoring the latest backups of Production databases on a various test servers is one of the frequent activity we perform on a regular basis, various application development teams often request a restore the latest backup on new or existing test or quality servers, many times we receive this kind of requests in the eleventh hour when packing up to go back home.

Solution

In this post we will see simple approach where we will
1. Schedule database backups on source server using T-SQL
2. Copy database backups to a destination server using third party tool.
3. Automate database restore on a destination server using T-SQL.

Before we get started we need to enable xp_cmdshell configuration setting on source server at instance level.

sp_configure 'show advanced options',1

reconfigure

sp_configure 'xp_cmdshell',1

reconfigure

1 – Schedule database backups on source server using T-SQL

Here`s the T-SQL script that is scheduled on the source server to perform full database backups. You need to set parameter @path to your backup location.

USE [master]

/****** Object: StoredProcedure [dbo].[AutoDBBackup] Script Date: 10/07/2019 11:04:13 ******/

CREATE PROCEDURE [dbo].[AutoDBBackup]

Begin

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'F:\AllDBBackups\FullDBBackups\' -- change the set @path = 'path to your backup location'.

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT [name] FROM sys.databases

WHERE [name] NOT IN ('master','tempdb','model','msdb') and state_desc='ONLINE'

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_db_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

WITH COPY_ONLY, NOFORMAT, INIT,SKIP, NOREWIND,

NOUNLOAD, COMPRESSION; --Please note that we are taking copy only backup.

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

End

2 - Copy database backups to a destination server using third party tool

There are many third party tools available to copy files from source to destination server using ftp. I am using free tool called FreeFileSync.
You can download this tool from https://freefilesync.org/download.php

3 - Automate database restore on a destination server using T-SQL.

Below T-SQL script is schedule on the destination server you wish to restore databases. The script will read the backup files from the directory and execute the restore command to restore databases. Also existing database on the instance will be replaced by latest database backups.

Please set following parameters in the script

- @restoreFromDir - - set database backup location

- @restoreToDataDir - - set data file location

- @restoreToLogDir - - set log file location

USE [master]

/****** Object: StoredProcedure [dbo].[AutoDBrestore] Script Date: 10/07/2019 16:52:58 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF

ALTER PROCEDURE [dbo].[AutoDBrestore]

Begin

DECLARE @kill varchar(8000) = '';

DECLARE @SPID INT;

set @SPID = (Select @@SPID)

--Print @SPID

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'

FROM sys.dm_exec_sessions where session_id > 51 and session_id <> @SPID

--print @kill

EXEC(@kill);

Declare

@restoreFromDir varchar(500),

@restoreToDataDir varchar(500)= NULL,

@restoreToLogDir varchar(500) = NULL,

@MatchFileList char(1) = 'N',

@OneDBName varchar(255) = NULL

set @restoreFromDir = 'F:\AllBackups\DB\'

set @restoreToDataDir = 'F:\AllBackups\DB\'

set @restoreToLogDir = 'F:\AllBackups\DB\'

set @MatchFileList = 'N'

set @OneDBName = NULL

--If a directory for the Log file is not supplied then use the data directory

If @restoreToLogDir is null

set @restoreToLogDir = @restoreToDataDir

set nocount on

declare @filename varchar(40),

@cmd varchar(5000),

@cmd2 varchar(5000),

@DataName varchar (255),

@LogName varchar (255),

@LogicalName varchar(255),

@PhysicalName varchar(255),

@Type varchar(20),

@FileGroupName varchar(255),

@Size varchar(20),

@MaxSize varchar(20),

@restoreToDir varchar(255),

@searchName varchar(255),

@DBName varchar(255),

@PhysicalFileName varchar(255)

create table #dirList (filename varchar(100))

create table #filelist (

LogicalName varchar(255),

PhysicalName varchar(255),

Type varchar(20),

FileGroupName varchar(255),

Size varchar(20),

MaxSize varchar(20),

FileId int,

CreateLSN bit,

DropLSN bit,

UniqueID varchar(255),

ReadOnlyLSn bit,

ReadWriteLSN bit,

backupSizeInBytes varchar(50),

SourceBlockSize int,

FileGroupid Int,

LogGroupGUID varchar(255),

DifferentialBaseLSN varchar(255),

DifferentialBaseGUID varchar(255),

isReadOnly bit,

IsPresent bit,

TDEThumbprint varchar(255)

)

--Get the list of database backups that are in the restoreFromDir directory

if @OneDBName is null

select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'

else

select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'

insert #dirList exec master..xp_cmdshell @cmd

select * from #dirList where filename like '%_db_%' --order by filename

if @OneDBName is null

declare BakFile_csr cursor for

select * from #dirList where filename like '%_db_%bak' order by filename

else

begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above

select @searchName = @OneDBName + '_db_%bak'

declare BakFile_csr cursor for

select top 1 * from #dirList where filename like @searchName

end

open BakFile_csr

fetch BakFile_csr into @filename

while @@fetch_status = 0

begin

select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'"

--insert #filelist

exec ( @cmd )

--select * from #filelist

--print @cmd

if @OneDBName is null

--DECLARE @dbName VARCHAR(255), @FILENAME varchar (255) = 'SCD_db_20160430.BAK'

select @dbName = left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)

else

select @dbName = @OneDBName

select @cmd = "RESTORE DATABASE " + @dbName +

" FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH "

PRINT ''

PRINT 'RESTORING DATABASE ' + @dbName

declare DataFileCursor cursor for

select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize

from #filelist

open DataFileCursor

fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

while @@fetch_status = 0

begin

if @MatchFileList != 'Y'

begin -- RESTORE with MOVE option

select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 ))

if @Type = 'L'

select @restoreToDir = @restoreToLogDir

else

select @restoreToDir = @restoreToDataDir

select @cmd = @cmd +

" MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', "

end

else

begin -- Match the file list, attempt to create any missing directory

select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) - patindex('%\%',reverse(@PhysicalName)) )

select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir

exec master..xp_cmdshell @cmd2

end

fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

end -- DataFileCursor loop

close DataFileCursor

deallocate DataFileCursor

select @cmd = @cmd + ' REPLACE'

--select @cmd 'command'

EXEC (@CMD)

truncate table #filelist

fetch BakFile_csr into @filename

end -- BakFile_csr loop

close BakFile_csr

deallocate BakFile_csr

drop table #dirList

drop table #filelist

return

End

Please note that you need to schedule database backup job followed by moving this backup files to destination server and later schedule restore job on destination server to refresh database. You need to estimate time duration required in each step to schedule it in a sequence.

SAMIR KAZI`S DBA BLOG