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
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
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