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
GO
sp_configure 'xp_cmdshell',1
reconfigure
GO


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]
GO
/****** Object:  StoredProcedure [dbo].[AutoDBBackup]    Script Date: 10/07/2019 11:04:13 ******/
CREATE PROCEDURE [dbo].[AutoDBBackup]
AS
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
GO


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]
GO
/****** Object:  StoredProcedure [dbo].[AutoDBrestore]    Script Date: 10/07/2019 16:52:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[AutoDBrestore]
AS
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.

No comments:

Post a Comment