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:
[expand title =”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
[/expand]
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:
[expand title =”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
[/expand]
As checking databases for integrity takes a lot of resources, we can omit it while creating a differential backup copy.
[expand title =”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
[/expand]
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:
[expand title =”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
[/expand]
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
