Implementing Automated Database Backup and Restore with Default Means

Total: 9 Average: 4

Introduction

You can find a lot of guides on how to backup and restore databases. In this one, we’ll show how this can be done using the default MS SQL Server means.

This example will cover a number of approaches – from checking the database’s integrity before backing it up to restoring the database from a previously created backup copy.

The solution

Firstly, let’s look at the overall algorithm we’ll use for backing up a database:

1) Defining which databases need to be backed up
2) Checking the integrity of the chosen databases
3) Creating a backup (full, differential or transaction log copy) for each of the chosen databases
4) Checking the created backup copies
5) Compressing the transaction logs (if needed)

Below, you can find an implementation example of this algorithm.

To define which databases need to be backed up, we’ll create the following table:

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[BackupSettings](
	[DBID] [int] NOT NULL,
	[FullPathBackup] [nvarchar](255) NOT NULL,
	[DiffPathBackup] [nvarchar](255) NULL,
	[LogPathBackup] [nvarchar](255) NULL,
	[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BackupSettings] PRIMARY KEY CLUSTERED 
(
	[DBID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

ALTER TABLE [srv].[BackupSettings] ADD  CONSTRAINT [DF_BackupSettings_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

The database identifier is located in the first column, ‘FullPathBackup’ contains the path for full backup copy creation (for example, ‘disk:\…\’), and the DiffPathBackup and LogPathBackup contain full paths for creation of differential and transaction log copies respectively. If the DiffPathBackup or LogPathBackup columns are empty, then the differential and/or transaction log copy for this database will not be created.

We can also create a representation based on this table:

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [srv].[vBackupSettings]
as
SELECT [DBID]
      ,DB_Name([DBID]) as [DBName]
	  ,[FullPathBackup]
      ,[DiffPathBackup]
      ,[LogPathBackup]
      ,[InsertUTCDate]
  FROM [srv].[BackupSettings];
GO

This representation allows you to effectively check which databases are participating in the backup process.

Now, let’s create a representation which displays database file information from the sys.master_files system representation:

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [inf].[ServerDBFileInfo] as
SELECT  @@Servername AS Server ,
        File_id ,--DB file identifier. The base value for file_id is 1
        Type_desc ,--Type file description
        Name as [FileName] ,--DB logical file name 
        LEFT(Physical_Name, 1) AS Drive ,--Drive flag of the DB file location
        Physical_Name ,--Full file name in the OS
        RIGHT(physical_name, 3) AS Ext ,--File extension
        Size as CountPage, --Current file size in 8Kb pages
		round((cast(Size*8 as float))/1024,3) as SizeMb, --File size in Mb
		round((cast(Size*8 as float))/1024/1024,3) as SizeGb, --File size in Gb
        case when is_percent_growth=0 then Growth*8 else 0 end as Growth, --File growth in 8Kb pages
		case when is_percent_growth=0 then round((cast(Growth*8 as float))/1024,3) end as GrowthMb, --File growth in Mb
		case when is_percent_growth=0 then round((cast(Growth*8 as float))/1024/1024,3) end as GrowthGb, --File growth in Gb
		case when is_percent_growth=1 then Growth else 0 end as GrowthPercent, --File growth in percent
		is_percent_growth, --Percent growth attribute
		database_id,
		DB_Name(database_id) as [DB_Name],
		State,--File state
		state_desc as StateDesc,--File state description
		is_media_read_only as IsMediaReadOnly,--File is located on the drive as read-only (0 - and for writing)
		is_read_only as IsReadOnly,--file is flagged as read-only (0 - and for writing)
		is_sparse as IsSpace,--Sparse file
		is_name_reserved as IsNameReserved,--1 - Remote file name, accessible for use.
		--It is necessary to get a log backup before using the same name (name or physical_name arguments) again for a new file
		--0 - Filename, inaccessible for use
		create_lsn as CreateLsn,--Transaction registration number in the log (LSN) which was used to create the file
		drop_lsn as DropLsn,--LSN which was used to delete the file
		read_only_lsn as ReadOnlyLsn,--LSN which was used by the file group containing the file to change the "read and write" type to "read-only" (the latest change)
		read_write_lsn as ReadWriteLsn,--LSN which was used by the file group containing the file to change the "read-only" type to "read and write" (the latest change)
		differential_base_lsn as DifferentialBaseLsn,--A base for differential backup copies. Data extents which were changed after the LSN is included into the differential backup.
		differential_base_guid as DifferentialBaseGuid,--Unique identifier of the base backup copy which will be used to create a differential copy.
		differential_base_time as DifferentialBaseTime,--The time corresponding to differential_base_lsn
		redo_start_lsn as RedoStartLsn,--LSN used to determine the start of the next redo
		--Is NULL, except for the cases in which state = RESTORING or state = RECOVERY_PENDING
		redo_start_fork_guid as RedoStartForkGuid,--Unique identifier for the restoration fork point
		--first_fork_guid argument value of the next restored backup copy should be equal to this value
		redo_target_lsn as RedoTargetLsn,--LSN which serves as a stop point for an "online" mode redo in this file
		--Is NULL, except for the cases in which state = RESTORING or state = RECOVERY_PENDING
		redo_target_fork_guid as RedoTargetForkGuid,--Restoration fork on which the container can be restored. Used along with redo_target_lsn
		backup_lsn as BackupLsn--LSN of the most recent data or the file's differential backup copy
FROM    sys.master_files--database_files;
GO

To create full backup copies, let’s implement the following stored procedure:

Code
USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [srv].[RunFullBackupDB]
	@ClearLog bit=1 --specifies whether the transaction log size should be reduced
AS
BEGIN
	/*
		Creating a full DB backup copy and checking the DB for integrity beforehand
	*/
	SET NOCOUNT ON;

    declare @dt datetime=getdate();
	declare @year int=YEAR(@dt);
	declare @month int=MONTH(@dt);
	declare @day int=DAY(@dt);
	declare @hour int=DatePart(hour, @dt);
	declare @minute int=DatePart(minute, @dt);
	declare @second int=DatePart(second, @dt);
	declare @pathBackup nvarchar(255);
	declare @pathstr nvarchar(255);
	declare @DBName nvarchar(255);
	declare @backupName nvarchar(255);
	declare @sql nvarchar(max);
	declare @backupSetId as int;
	declare @FileNameLog nvarchar(255);

	declare @tbllog table(
		[DBName] [nvarchar](255) NOT NULL,
		[FileNameLog] [nvarchar](255) NOT NULL
	);
	
	declare @tbl table (
		[DBName] [nvarchar](255) NOT NULL,
		[FullPathBackup] [nvarchar](255) NOT NULL
	);
	
	--Retrieving DB name and full paths for full backup copy creation
	insert into @tbl (
	           [DBName]
	           ,[FullPathBackup]
	)
	select		DB_NAME([DBID])
	           ,[FullPathBackup]
	from [srv].[BackupSettings];

	--Retrieving the DB name and names of the according transaction logs (as one DB can have multiple logs)
	insert into @tbllog([DBName], [FileNameLog])
	select t.[DBName], tt.[FileName] as [FileNameLog]
	from @tbl as t
	inner join [inf].[ServerDBFileInfo] as tt on t.[DBName]=DB_NAME(tt.[database_id])
	where tt.[Type_desc]='LOG';
	
	--sequentially processing each of the DBs we got earlier
	while(exists(select top(1) 1 from @tbl))
	begin
		set @backupSetId=NULL;

		select top(1)
		@DBName=[DBName],
		@pathBackup=[FullPathBackup]
		from @tbl;
	
		set @backupName=@DBName+N'_Full_backup_'+cast(@year as nvarchar(255))+N'_'+cast(@month as nvarchar(255))+N'_'+cast(@day as nvarchar(255))--+N'_'
						--+cast(@hour as nvarchar(255))+N'_'+cast(@minute as nvarchar(255))+N'_'+cast(@second as nvarchar(255));
		set @pathstr=@pathBackup+@backupName+N'.bak';

		--checking the DB for integrity
		set @sql=N'DBCC CHECKDB(N'+N''''+@DBName+N''''+N')  WITH NO_INFOMSGS';

		exec(@sql);
		
		--executing the backup copy creation procedure
		set @sql=N'BACKUP DATABASE ['+@DBName+N'] TO DISK = N'+N''''+@pathstr+N''''+
				 N' WITH NOFORMAT, NOINIT, NAME = N'+N''''+@backupName+N''''+
				 N', CHECKSUM, STOP_ON_ERROR, SKIP, REWIND, COMPRESSION, STATS = 10;';
	
		exec(@sql);

		--checking the backup copy we created
		select @backupSetId = position
		from msdb..backupset where database_name=@DBName
		and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@DBName);

		set @sql=N'Verification error. Backup copy information for "'+@DBName+'" database not found.';

		if @backupSetId is null begin raiserror(@sql, 16, 1) end
		else
		begin
			set @sql=N'RESTORE VERIFYONLY FROM DISK = N'+''''+@pathstr+N''''+N' WITH FILE = '+cast(@backupSetId as nvarchar(255));

			exec(@sql);
		end

		--compressing the DB transaction logs
		if(@ClearLog=1)
		begin
			while(exists(select top(1) 1 from @tbllog where [DBName]=@DBName))
			begin
				select top(1)
				@FileNameLog=FileNameLog
				from @tbllog
				where DBName=@DBName;
			
				set @sql=N'USE ['+@DBName+N'];'+N' DBCC SHRINKFILE (N'+N''''+@FileNameLog+N''''+N' , 0, TRUNCATEONLY)';

				exec(@sql);

				delete from @tbllog
				where FileNameLog=@FileNameLog
				and DBName=@DBName;
			end
		end
		
		delete from @tbl
		where [DBName]=@DBName;
	end
END
GO

According to the code, we can see that this procedure provides a solution for the remaining steps of the backup copy creation algorithm.

Procedures which create differential and transaction log copies are implemented in a similar way:

Code
USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [srv].[RunDiffBackupDB]
	@ClearLog bit=1 --specifies if the transaction log size should be reduced
AS
BEGIN
	/*
		Creating a differential DB backup copy
	*/
	SET NOCOUNT ON;

    declare @dt datetime=getdate();
	declare @year int=YEAR(@dt);
	declare @month int=MONTH(@dt);
	declare @day int=DAY(@dt);
	declare @hour int=DatePart(hour, @dt);
	declare @minute int=DatePart(minute, @dt);
	declare @second int=DatePart(second, @dt);
	declare @pathBackup nvarchar(255);
	declare @pathstr nvarchar(255);
	declare @DBName nvarchar(255);
	declare @backupName nvarchar(255);
	declare @sql nvarchar(max);
	declare @backupSetId as int;
	declare @FileNameLog nvarchar(255);
	
	declare @tbl table (
		[DBName] [nvarchar](255) NOT NULL,
		[DiffPathBackup] [nvarchar](255) NOT NULL
	);

	declare @tbllog table(
		[DBName] [nvarchar](255) NOT NULL,
		[FileNameLog] [nvarchar](255) NOT NULL
	);
	
	--Retrieving the DB name and full paths for creating differential backup copies
	insert into @tbl (
	           [DBName]
	           ,[DiffPathBackup]
	)
	select		DB_NAME([DBID])
	           ,[DiffPathBackup]
	from [srv].[BackupSettings]
	where [DiffPathBackup] is not null;

	--Retrieving DB name and the full names of the according transaction log files (as one DB can have multiple logs)
	insert into @tbllog([DBName], [FileNameLog])
	select t.[DBName], tt.[FileName] as [FileNameLog]
	from @tbl as t
	inner join [inf].[ServerDBFileInfo] as tt on t.[DBName]=DB_NAME(tt.[database_id])
	where tt.[Type_desc]='LOG';
	
	--sequentially processing each of the DBs we got earlier
	while(exists(select top(1) 1 from @tbl))
	begin
		set @backupSetId=NULL;

		select top(1)
		@DBName=[DBName],
		@pathBackup=[DiffPathBackup]
		from @tbl;
	
		set @backupName=@DBName+N'_Diff_backup_'+cast(@year as nvarchar(255))+N'_'+cast(@month as nvarchar(255))+N'_'+cast(@day as nvarchar(255))+N'_'
						+cast(@hour as nvarchar(255))+N'_'+cast(@minute as nvarchar(255))+N'_'+cast(@second as nvarchar(255));
		set @pathstr=@pathBackup+@backupName+N'.bak';
		
		--checking the DB for integrity
		set @sql=N'DBCC CHECKDB(N'+N''''+@DBName+N''''+N')  WITH NO_INFOMSGS';

		exec(@sql);
		
		--executing the backup procedure
		set @sql=N'BACKUP DATABASE ['+@DBName+N'] TO DISK = N'+N''''+@pathstr+N''''+
				 N' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'+N''''+@backupName+N''''+
				 N', CHECKSUM, STOP_ON_ERROR, SKIP, REWIND, COMPRESSION, STATS = 10;';
	
		exec(@sql);

		--checking the backup copy we just created
		select @backupSetId = position
		from msdb..backupset where database_name=@DBName
		and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@DBName);

		set @sql=N'Verification error. Backup copy information for "'+@DBName+'" database not found.';

		if @backupSetId is null begin raiserror(@sql, 16, 1) end
		else
		begin
			set @sql=N'RESTORE VERIFYONLY FROM DISK = N'+''''+@pathstr+N''''+N' WITH FILE = '+cast(@backupSetId as nvarchar(255));

			exec(@sql);
		end

		--compressing the DB transaction logs
		if(@ClearLog=1)
		begin
			while(exists(select top(1) 1 from @tbllog where [DBName]=@DBName))
			begin
				select top(1)
				@FileNameLog=FileNameLog
				from @tbllog
				where DBName=@DBName;
			
				set @sql=N'USE ['+@DBName+N'];'+N' DBCC SHRINKFILE (N'+N''''+@FileNameLog+N''''+N' , 0, TRUNCATEONLY)';

				exec(@sql);

				delete from @tbllog
				where FileNameLog=@FileNameLog
				and DBName=@DBName;
			end
		end
		
		delete from @tbl
		where [DBName]=@DBName;
	end
END
GO

As checking databases for integrity takes a lot of resources, we can omit it while creating a differential backup copy.

Code
USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [srv].[RunLogBackupDB]
	@ClearLog bit=1 --specifies if the transaction log size should be reduced
AS
BEGIN
	/*
		Backing up the DB transaction log
	*/
	SET NOCOUNT ON;

    declare @dt datetime=getdate();
	declare @year int=YEAR(@dt);
	declare @month int=MONTH(@dt);
	declare @day int=DAY(@dt);
	declare @hour int=DatePart(hour, @dt);
	declare @minute int=DatePart(minute, @dt);
	declare @second int=DatePart(second, @dt);
	declare @pathBackup nvarchar(255);
	declare @pathstr nvarchar(255);
	declare @DBName nvarchar(255);
	declare @backupName nvarchar(255);
	declare @sql nvarchar(max);
	declare @backupSetId as int;
	declare @FileNameLog nvarchar(255);
	
	declare @tbl table (
		[DBName] [nvarchar](255) NOT NULL,
		[LogPathBackup] [nvarchar](255) NOT NULL
	);

	declare @tbllog table(
		[DBName] [nvarchar](255) NOT NULL,
		[FileNameLog] [nvarchar](255) NOT NULL
	);
	
	--Retrieving DB names and full paths for creating backup copies of transaction logs with a non-simple recovery model (full or bulk-logged). System DBs are also excluded
	insert into @tbl (
	           [DBName]
	           ,[LogPathBackup]
	)
	select		DB_NAME(b.[DBID])
	           ,b.[LogPathBackup]
	from [srv].[BackupSettings] as b
	inner join sys.databases as d on b.[DBID]=d.[database_id]
	where d.recovery_model<3
	and DB_NAME([DBID]) not in (
		N'master',
		N'tempdb',
		N'model',
		N'msdb',
		N'ReportServer',
		N'ReportServerTempDB'
	)
	and [LogPathBackup] is not null;

	--Retrieving DB name and the full names of the according transaction log files (as one DB can have multiple logs)
	insert into @tbllog([DBName], [FileNameLog])
	select t.[DBName], tt.[FileName] as [FileNameLog]
	from @tbl as t
	inner join [inf].[ServerDBFileInfo] as tt on t.[DBName]=DB_NAME(tt.[database_id])
	where tt.[Type_desc]='LOG';
	
	--sequentially processing each of the DBs we got earlier
	while(exists(select top(1) 1 from @tbl))
	begin
		set @backupSetId=NULL;

		select top(1)
		@DBName=[DBName],
		@pathBackup=[LogPathBackup]
		from @tbl;
	
		set @backupName=@DBName+N'_Log_backup_'+cast(@year as nvarchar(255))+N'_'+cast(@month as nvarchar(255))+N'_'+cast(@day as nvarchar(255))+N'_'
						+cast(@hour as nvarchar(255))+N'_'+cast(@minute as nvarchar(255))+N'_'+cast(@second as nvarchar(255));
		set @pathstr=@pathBackup+@backupName+N'.trn';
		
		--executing the backup procedure
		set @sql=N'BACKUP LOG ['+@DBName+N'] TO DISK = N'+N''''+@pathstr+N''''+
				 N' WITH NOFORMAT, NOINIT, NAME = N'+N''''+@backupName+N''''+
				 N', CHECKSUM, STOP_ON_ERROR, SKIP, REWIND, COMPRESSION, STATS = 10;';
	
		exec(@sql);

		--Checking the transaction log backup copy we just created
		select @backupSetId = position
		from msdb..backupset where database_name=@DBName
		and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@DBName);

		set @sql=N'Verification error. Backup copy information for "'+@DBName+'" database not found.';

		if @backupSetId is null begin raiserror(@sql, 16, 1) end
		else
		begin
			set @sql=N'RESTORE VERIFYONLY FROM DISK = N'+''''+@pathstr+N''''+N' WITH FILE = '+cast(@backupSetId as nvarchar(255));

			exec(@sql);
		end

		--compressing the DB transaction logs
		if(@ClearLog=1)
		begin
			while(exists(select top(1) 1 from @tbllog where [DBName]=@DBName))
			begin
				select top(1)
				@FileNameLog=FileNameLog
				from @tbllog
				where DBName=@DBName;
			
				set @sql=N'USE ['+@DBName+N'];'+N' DBCC SHRINKFILE (N'+N''''+@FileNameLog+N''''+N' , 0, TRUNCATEONLY)';

				exec(@sql);

				delete from @tbllog
				where FileNameLog=@FileNameLog
				and DBName=@DBName;
			end
		end
		
		delete from @tbl
		where [DBName]=@DBName;
	end
END
GO

As said above, checking databases for integrity is a resource-heavy task. Combined with the fact that transaction log backup copies usually need to be created quite often, this gives us a reason to omit integrity checking while creating a transaction log copy.

Please also keep in mind that full backup copies of ‘master’, ‘msdb’ and ‘model’ databases need to be done periodically.

To automate the backup copy creation process, you’ll just need to place a call of the previously implemented procedures into the Windows Task Scheduler, agent jobs or any similar available service.

You will need to set the call frequency for each of those procedures individually based on the load peaks, activity plateaus etc.

The basic approach is as follows:

1) Creating a full backup copy once a day
2) Creating differential backup copies every 2-4 hours
3) Creating transaction log backup copies every 5-60 minutes

Please keep in mind that usually databases participate in the fail-safe and quick access system. And, if the later uses transaction log backup copies, it is vitally important not to interfere with the procedure. More specifically, this means that transaction log copies should not be created by several different processes – if this happens, the backup sequence from these copies will be lost.

Here, we have seen examples of each database being processed sequentially, one at a time. However, we can achieve parallel processing in production environment – allowing for several backup copies to be made simultaneously. This can be approached in a few different ways. For example, by calling the following stored procedure:

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [inf].[RunAsyncExecute]
(
	@sql nvarchar(max),
	@jobname nvarchar(57) = null,   
	@database nvarchar(128)= null,
	@owner nvarchar(128) = null
)
AS BEGIN
/*
	Asynchronous package execution via the Agent's jobs
	RunAsyncExecute - asynchronous execution of T-SQL command or stored prodecure  
	2012 Antonin Foller, Motobit Software, www.motobit.com
	http://www.motobit.com/tips/detpg_async-execute-sql/  
*/  
    SET NOCOUNT ON;  
  
    declare @id uniqueidentifier;

    --Create unique job name if the name is not specified  
    if (@jobname is null) set @jobname= '';

    set @jobname = @jobname + '_async_' + convert(varchar(64),NEWID());
  
    if (@owner is null) set @owner = 'sa';
  
    --Create a new job, get job ID  
    execute msdb..sp_add_job @jobname, @owner_login_name=@owner, @job_id=@id OUTPUT;
  
    --Specify a job server for the job  
    execute msdb..sp_add_jobserver @job_id=@id;
  
    --Specify a first step of the job - the SQL command  
    --(@on_success_action = 3 ... Go to next step)  
    execute msdb..sp_add_jobstep @job_id=@id, @step_name='Step1', @command = @sql,   
        @database_name = @database, @on_success_action = 3;
  
    --Specify next step of the job - delete the job  
    declare @deletecommand varchar(200);

    set @deletecommand = 'execute msdb..sp_delete_job @job_name='''+@jobname+'''';

    execute msdb..sp_add_jobstep @job_id=@id, @step_name='Step2', @command = @deletecommand;
  
    --Start the job  
    execute msdb..sp_start_job @job_id=@id;
  
END  
GO

Here, asynchrony is achieved by dynamically creating the Agent jobs, executing and deleting them afterwards.

Now, let’s look at the general algorithm for restoring databases from backup copies previously created in a different/test environment:

1) Defining which databases should be restored and the location of their backup copies
2) Restoring the databases
3) Checking the restored databases for integrity

Now, we’ll look at an implementation of an algorithm which restores a database from a full backup copy. For a differential copy, the procedure is similar – the only difference being that a full backup copy needs to be restored firsthand, followed by the differential copy.

To define which databases should be restored, as well as the location of their backup copies, let’s create two tables as shown below:

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[RestoreSettings](
	[DBName] [nvarchar](255) NOT NULL,
	[FullPathRestore] [nvarchar](255) NOT NULL,
	[DiffPathRestore] [nvarchar](255) NOT NULL,
	[LogPathRestore] [nvarchar](255) NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_RestoreSettings] PRIMARY KEY CLUSTERED 
(
	[DBName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

ALTER TABLE [srv].[RestoreSettings] ADD  CONSTRAINT [DF_RestoreSettings_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

Here, the purpose of the columns is analogous to those from the [srv].[BackupSettings] table. The only difference being that the full path will be used to locate the backup copies for restoration, and not to create new ones.

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[RestoreSettingsDetail](
	[Row_GUID] [uniqueidentifier] NOT NULL,
	[DBName] [nvarchar](255) NOT NULL,
	[SourcePathRestore] [nvarchar](255) NOT NULL,
	TargetPathRestore [nvarchar](255) NOT NULL,
	[Ext] [nvarchar](255) NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_RestoreSettingsDetail] PRIMARY KEY CLUSTERED 
(
	[Row_GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

ALTER TABLE [srv].[RestoreSettingsDetail] ADD  CONSTRAINT [DF_RestoreSettingsDetail_Row_GUID]  DEFAULT (newid()) FOR [Row_GUID];
GO

ALTER TABLE [srv].[RestoreSettingsDetail] ADD  CONSTRAINT [DF_RestoreSettingsDetail_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

This table is needed to define the complete file names of the database being restored, which are then used for further transfer (for example, [SourcePathRestore]=’Logical file name’ and [TargetPathRestore]= ‘disk:\…\Physical file name’, while [Ext]= ‘File extension’)

Actually, we can define logical names of the database files using the following query:

RESTORE FILELISTONLY 
FROM DISK ='disk:\...\backup copy.BAK';

Getting information about backup copies located in a file can be done in this way:

RESTORE HEADERONLY
FROM DISK='disk:\...\backup copy.BAK';

Next, we have an implementation of a stored procedure used for restoring a database from a full backup copy and checking it for data integrity:

Code
USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [srv].[RunFullRestoreDB]
AS
BEGIN
	/*
		Recovering a DB from a full backup copy and checking the DB for integrity
	*/
	SET NOCOUNT ON;

    declare @dt datetime=DateAdd(day,-2,getdate());
	declare @year int=YEAR(@dt);
	declare @month int=MONTH(@dt);
	declare @day int=DAY(@dt);
	declare @hour int=DatePart(hour, @dt);
	declare @minute int=DatePart(minute, @dt);
	declare @second int=DatePart(second, @dt);
	declare @pathBackup nvarchar(255);
	declare @pathstr nvarchar(255);
	declare @DBName nvarchar(255);
	declare @backupName nvarchar(255);
	declare @sql nvarchar(max);
	declare @backupSetId as int;
	declare @FileNameLog nvarchar(255);
	declare @SourcePathRestore nvarchar(255);
	declare @TargetPathRestore nvarchar(255);
	declare @Ext nvarchar(255);
	
	declare @tbl table (
		[DBName] [nvarchar](255) NOT NULL,
		[FullPathRestore] [nvarchar](255) NOT NULL
	);

	declare @tbl_files table (
		[DBName] [nvarchar](255) NOT NULL,
		[SourcePathRestore] [nvarchar](255) NOT NULL,
		[TargetPathRestore] [nvarchar](255) NOT NULL,
		[Ext] [nvarchar](255) NOT NULL
	);
	
	--retrieving a list of DB names and the paths to full backup copies
	insert into @tbl (
	           [DBName]
	           ,[FullPathRestore]
	)
	select		[DBName]
	           ,[FullPathRestore]
	from [srv].[RestoreSettings];

	--retrieving detailed info about the new DB files location
	insert into @tbl_files (
	           [DBName]
	           ,[SourcePathRestore]
			   ,[TargetPathRestore]
			   ,[Ext]
	)
	select		[DBName]
	           ,[SourcePathRestore]
			   ,[TargetPathRestore]
			   ,[Ext]
	from [srv].[RestoreSettingsDetail];
	
	--processing each of the DBs we got earlier
	while(exists(select top(1) 1 from @tbl))
	begin
		set @backupSetId=NULL;

		select top(1)
		@DBName=[DBName],
		@pathBackup=[FullPathRestore]
		from @tbl;
	
		set @backupName=@DBName+N'_Full_backup_'+cast(@year as nvarchar(255))+N'_'+cast(@month as nvarchar(255))+N'_'+cast(@day as nvarchar(255))--+N'_'
						--+cast(@hour as nvarchar(255))+N'_'+cast(@minute as nvarchar(255))+N'_'+cast(@second as nvarchar(255));
		set @pathstr=@pathBackup+@backupName+N'.bak';

		--creating a backup query and executing it
		set @sql=N'RESTORE DATABASE ['+@DBName+N'_Restore] FROM DISK = N'+N''''+@pathstr+N''''+
				 N' WITH FILE = 1,';

		while(exists(select top(1) 1 from @tbl_files where [DBName]=@DBName))
		begin
			select top(1)
			@SourcePathRestore=[SourcePathRestore],
			@TargetPathRestore=[TargetPathRestore],
			@Ext=[Ext]
			from @tbl_files
			where [DBName]=@DBName;

			set @sql=@sql+N' MOVE N'+N''''+@SourcePathRestore+N''''+N' TO N'+N''''+@TargetPathRestore+N'_Restore.'+@Ext+N''''+N',';

			delete from @tbl_files
			where [DBName]=@DBName
			and [SourcePathRestore]=@SourcePathRestore
			and [Ext]=@Ext;
		end

		set @sql=@sql+N' NOUNLOAD,  REPLACE,  STATS = 5';

		exec(@sql);

		--checking the DB for integrity
		set @sql=N'DBCC CHECKDB(N'+N''''+@DBName+'_Restore'+N''''+N')  WITH NO_INFOMSGS';
	
		exec(@sql);
		
		delete from @tbl
		where [DBName]=@DBName;
	end
END

To specify which full backup copy should be used for restoration, a specially structured filename is used:

<databse name>_Full_backup_<year>_<month_number>_<day_number>.bak

To automate this database restoration process, the call of the stored procedure we implemented should be placed into the Windows Task Scheduler, Agent jobs or any similar available service.

You can see the most recent database backup copies using the following representation:

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [inf].[vServerLastBackupDB] as
with backup_cte as
(
    select
        bs.[database_name],
        backup_type =
            case bs.[type]
                when 'D' then 'database'
                when 'L' then 'log'
                when 'I' then 'differential'
                else 'other'
            end,
        bs.[first_lsn],
		bs.[last_lsn],
		bs.[backup_start_date],
		bs.[backup_finish_date],
		cast(bs.[backup_size] as decimal(18,3))/1024/1024 as BackupSizeMb,
        rownum = 
            row_number() over
            (
                partition by bs.[database_name], type 
                order by bs.[backup_finish_date] desc
            ),
		LogicalDeviceName = bmf.[logical_device_name],
		PhysicalDeviceName = bmf.[physical_device_name],
		bs.[server_name],
		bs.[user_name]
    FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf 
        ON [bs].[media_set_id] = [bmf].[media_set_id]
)
select
    [server_name] as [ServerName],
	[database_name] as [DBName],
	[user_name] as [USerName],
    [backup_type] as [BackupType],
	[backup_start_date] as [BackupStartDate],
    [backup_finish_date] as [BackupFinishDate],
	[BackupSizeMb], --uncompressed size
	[LogicalDeviceName],
	[PhysicalDeviceName],
	[first_lsn] as [FirstLSN],
	[last_lsn] as [LastLSN]
from backup_cte
where rownum = 1;

The result

In this guide, we have looked at an implementation of automated backup process on one server and consequent restore on a different one (a test server, for example).

This method allows us to automate the backup copy creation process, to check the backup copies by restoring them and fine-tune the processes shown above.

Sources:

Backup
Restore
Backupset
CHECKDB
SHRINKFILE
sys.master_files

Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.