An example of the implementation of an automated process for backing up and restoring databases with built-in tools
Foreword
On the Internet you can find a lot of examples on creating backup copies of databases, as well as on their recovery. Let us give one more example of the built-in tools in MS SQL Server.
In this example, several approaches will be collected at once — from checking the integrity of the database before creating a backup to restoring this database from an already created backup.
Decision
First we give the general algorithm for creating a backup:
1) Determine which databases need to be backed up
2) We check each selected database for integrity.
3) Create a backup for each selected database (full or differential (differential), or transaction log)
4) Check the received backups
5) Compress the transaction logs of used databases (if necessary)
')
Next, we give an example of the implementation of the above algorithm.
In order to determine which databases need to be backed up, create the following table:
Backup Settings Table
USE [_] 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 first column indicates the database identifier, FullPathBackup contains the full path for creating full backups (for example, 'drive: \ ... \'), and DiffPathBackup and LogPathBackup have full paths for creating differential backups and transaction log backups, respectively . If the DiffPathBackup or LogPathBackup column is empty, the database will not participate in the creation of a differential backup or transaction log backup, respectively.
You can also create a view based on this table:
View for backup settings
USE [_] 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 view allows you to quickly see which databases are involved in the backup.
Now we will create a view that displays information on the database files from the sys.master_files system view:
DB file view
USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[ServerDBFileInfo] as SELECT @@Servername AS Server , File_id ,-- . file_id 1 Type_desc ,-- Name as [FileName] ,-- LEFT(Physical_Name, 1) AS Drive ,-- , Physical_Name ,-- RIGHT(physical_name, 3) AS Ext ,-- Size as CountPage, -- 8 round((cast(Size*8 as float))/1024,3) as SizeMb, -- round((cast(Size*8 as float))/1024/1024,3) as SizeGb, -- case when is_percent_growth=0 then Growth*8 else 0 end as Growth, -- 8 case when is_percent_growth=0 then round((cast(Growth*8 as float))/1024,3) end as GrowthMb, -- case when is_percent_growth=0 then round((cast(Growth*8 as float))/1024/1024,3) end as GrowthGb, -- case when is_percent_growth=1 then Growth else 0 end as GrowthPercent, -- is_percent_growth, -- database_id, DB_Name(database_id) as [DB_Name], State,-- state_desc as StateDesc,-- is_media_read_only as IsMediaReadOnly,-- (0- ) is_read_only as IsReadOnly,-- (0- ) is_sparse as IsSpace,-- is_name_reserved as IsNameReserved,--1 - , . -- , ( name physical_name) --0 - , create_lsn as CreateLsn,-- (LSN), drop_lsn as DropLsn,-- LSN, read_only_lsn as ReadOnlyLsn,-- LSN, , , « » « » ( ) read_write_lsn as ReadWriteLsn,-- LSN, , , « » « » ( ) differential_base_lsn as DifferentialBaseLsn,-- . , , LSN differential_base_guid as DifferentialBaseGuid,-- , differential_base_time as DifferentialBaseTime,--, differential_base_lsn redo_start_lsn as RedoStartLsn,-- LSN, -- NULL, , state = RESTORING state = RECOVERY_PENDING redo_start_fork_guid as RedoStartForkGuid,-- . -- first_fork_guid . redo_target_lsn as RedoTargetLsn,-- LSN, « » -- NULL, , state = RESTORING state = RECOVERY_PENDING redo_target_fork_guid as RedoTargetForkGuid,-- , . redo_target_lsn backup_lsn as BackupLsn-- LSN FROM sys.master_files--database_files; GO To create full backups, we implement the stored procedure:
The procedure for creating full backups
USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [srv].[RunFullBackupDB] @ClearLog bit=1 -- AS BEGIN /* */ 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 ); -- insert into @tbl ( [DBName] ,[FullPathBackup] ) select DB_NAME([DBID]) ,[FullPathBackup] from [srv].[BackupSettings]; -- ( ) 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'; -- 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'; -- set @sql=N'DBCC CHECKDB(N'+N''''+@DBName+N''''+N') WITH NO_INFOMSGS'; exec(@sql); -- 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); -- 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' . "'+@DBName+'" .'; 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 -- 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 By code, it is clear that this stored procedure immediately resolves all remaining points of the algorithm for creating full backups.
Similarly implemented stored procedures for creating differential backups and backup transaction logs:
The procedure for creating differential backups database
USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [srv].[RunDiffBackupDB] @ClearLog bit=1 -- AS BEGIN /* */ 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 ); -- insert into @tbl ( [DBName] ,[DiffPathBackup] ) select DB_NAME([DBID]) ,[DiffPathBackup] from [srv].[BackupSettings] where [DiffPathBackup] is not null; -- ( ) 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'; -- 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'; -- set @sql=N'DBCC CHECKDB(N'+N''''+@DBName+N''''+N') WITH NO_INFOMSGS'; exec(@sql); -- 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); -- 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' . "'+@DBName+'" .'; 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 -- 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 T to check the integrity of the database is a fairly resource-intensive task, then to improve performance, you can not check the integrity of the database before creating a differential backup database.
Procedure for backing up transaction logs
USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [srv].[RunLogBackupDB] @ClearLog bit=1 -- AS BEGIN /* */ 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 ); -- , ( ) -- 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; -- ( ) 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'; -- 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'; -- 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); -- 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' . "'+@DBName+'" .'; 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 -- 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 Since T to back up the transaction log is usually done quite often, and checking the integrity of the database is a fairly resource-intensive task, they usually do not check the integrity of the database before creating a backup copy of the transaction log.
It is also necessary to remember that periodically you need to make full backup copies of the master, msdb and model databases.
To automate the process of creating backups, it is enough to place a call to the stored procedures implemented above in Windows Task Scheduler, in Agent tasks or in any other available service.
The frequency of calls for each stored procedure must be selected individually, based on load peaks, idle periods, etc.
The most common approach:
1) Creating a full backup once a day
2) Creating differential backups every 2-4 hours
3) Create backup copies of the transaction log every 5-60 minutes
It is also important to remember that databases usually participate in fault tolerance and rapid availability. And if the latter uses backup copies of transaction logs, then it is important not to interfere with this process (that is, you should not allow the creation of backup copies of the database transaction log by different processes, t then the sequence will be lost to restore these backups).
Here were examples of the sequential processing of each database. But in production it is quite possible to parallelize processing, making several backups at the same time. This can be done in different ways. For example, by calling the following stored procedure:
Procedure for asynchronous calling requests
USE [_] 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 /* 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's tasks with their subsequent execution and deletion.
Now we give a general algorithm for restoring a database from previously created backups (in a different or test environment):
1) Determine which databases need to be restored, as well as the location of backup copies
2) Restoring databases
3) Check the integrity of the restored databases
Below is an example implementation of the algorithm for restoring a database from a full backup. For a differential procedure, the procedure is the same, but the full backup is restored first and then the differential one.
In order to determine which databases need to be restored, as well as the location of the backups, create two tables:
DB Recovery Settings Table
USE [_] 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 column assignments are similar to the column assignments for the [srv]. [BackupSettings] table with the only difference that the full path will not create backup copies, but take existing ones for recovery.
Recovery database table
USE [_] 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 determine the full file names of the restored database for later transfer (for example, [SourcePathRestore] = 'Logical file name' and [TargetPathRestore] = 'drive: \ ... \ Physical file name', and [Ext] = ' File extension'.
In fact, here you can determine the logical names of the database files by the following query:
Getting logical database file names
RESTORE FILELISTONLY FROM DISK =':\...\ .BAK'; And you can get information about the backups that are in the file as follows:
Retrieving database backup information
RESTORE HEADERONLY FROM DISK=':\...\ .BAK'; Now we give an example of the implementation of the stored procedure to restore the database from a full backup with the subsequent check for data integrity:
The procedure for restoring the database for full backup
USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [srv].[RunFullRestoreDB] AS BEGIN /* */ 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 ); -- insert into @tbl ( [DBName] ,[FullPathRestore] ) select [DBName] ,[FullPathRestore] from [srv].[RestoreSettings]; -- , insert into @tbl_files ( [DBName] ,[SourcePathRestore] ,[TargetPathRestore] ,[Ext] ) select [DBName] ,[SourcePathRestore] ,[TargetPathRestore] ,[Ext] from [srv].[RestoreSettingsDetail]; -- 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'; -- 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); -- set @sql=N'DBCC CHECKDB(N'+N''''+@DBName+'_Restore'+N''''+N') WITH NO_INFOMSGS'; exec(@sql); delete from @tbl where [DBName]=@DBName; end END Here, to determine which full backup to restore, the file name is taken, which is formed as follows:
<database name> _Full_backup_ <year> _ <month_number_ in_year> _ <day_number_in_month> .bak
To automate the process of restoring the database from full backups, it is enough to place a call to the stored procedure implemented above in Windows Task Scheduler, in Agent tasks or in any other available service.
You can view the latest backup copies of the database using the following view:
Presentation of the latest database backup
USE [_] 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], -- [LogicalDeviceName], [PhysicalDeviceName], [first_lsn] as [FirstLSN], [last_lsn] as [LastLSN] from backup_cte where rownum = 1; Result
This article described an example of implementing an automated backup process on one server and then restoring it on another server (for example, a test server).
This method allows you to automate the process of creating backups, check backups using the restore method, and also fine-tune the above processes.
Sources:
» Lecture 5: Complete Recovery Model
» Backup
» Restore
» Backupset
» CHECKDB
» SHRINKFILE
» sys.master_files
» sp_async_execute
Source: https://habr.com/ru/post/343554/
All Articles