まえがき
インターネットでは、データベースのバックアップコピーの作成や復元に関する多くの例を見つけることができます。 MS SQL Serverの組み込みツールを使用した別の例を次に示します。
この例では、バックアップを作成する前にデータベースの整合性をチェックすることから、以前に作成したバックアップからこのデータベースを復元することまで、いくつかのアプローチが一度に収集されます。
解決策
まず、バックアップを作成するための一般的なアルゴリズムを示します。
1）バックアップするデータベースを決定する
2）選択した各データベースの整合性をチェックします
3）選択した各データベース（完全または差分（差分）、またはトランザクションログ）のバックアップコピーを作成します
4）受信したバックアップを確認します
5）ワークアウトされたデータベースのトランザクションログを圧縮する（必要な場合）
以下は、上記のアルゴリズムの実装例です。
バックアップするデータベースを決定するには、次の表を作成します。
バックアップ設定表
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
最初の列はデータベース識別子を示し、FullPathBackupにはフルバックアップを作成するためのフルパス（たとえば、 'drive：\ ... \'）、差分バックアップおよびトランザクションログのバックアップを作成するためのDiffPathBackupおよびLogPathBackupフルパスがそれぞれ含まれます。 。 DiffPathBackup列またはLogPathBackup列が空の場合、データベースはそれぞれ差分バックアップまたはトランザクションログバックアップの作成に参加しません。
この表に基づいてビューを作成することもできます。
バックアップ設定の表示
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
このビューは、バックアップに関係するデータベースをすばやく確認する機会を提供します。
sys.master_filesシステムビューからデータベースファイルに関する情報を表示するビューを作成します。
データベースファイルの表現
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
完全バックアップを作成するには、ストアドプロシージャを実装します。
完全バックアップ手順
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
コードは、このストアドプロシージャが完全バックアップを作成するためのアルゴリズムの残りのすべてのポイントを直ちに解決することを示しています。
同様に、差分バックアップとトランザクションログのバックアップを作成するためのストアドプロシージャが実装されています。
差分データベースバックアップを作成する手順
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
データベースの整合性のチェックはかなりリソースを消費するタスクなので、パフォーマンスを向上させるために、データベースの差分バックアップを作成する前にデータベースの整合性をチェックすることはできません。
トランザクションログのバックアップ手順
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
通常、トランザクションログのバックアップは非常に頻繁に行われ、データベースの整合性のチェックはかなりリソースを消費するタスクであるため、通常はトランザクションログのバックアップを作成する前にデータベースの整合性をチェックしません。
また、master、msdb、およびmodelデータベースの完全バックアップを定期的に作成する必要があることを忘れないでください。
バックアップコピーの作成プロセスを自動化するには、上記で実装したストアドプロシージャの呼び出しをWindowsタスクスケジューラ、エージェントタスク、または他の利用可能なサービスに配置するだけで十分です。
各ストアドプロシージャの呼び出し頻度は、ピーク負荷、非アクティブ期間などに基づいて個別に選択する必要があります。
最も一般的なアプローチ：
1）1日1回完全バックアップを作成する
2）2〜4時間ごとに差分バックアップを作成する
3）トランザクションログを5〜60分ごとにバックアップする
通常、データベースはフォールトトレランスと高速可用性に関与していることを覚えておくことが重要です。 後者がトランザクションログのバックアップコピーを使用する場合、このプロセスに干渉しないことが重要です（つまり、データベーストランザクションログのバックアップが異なるプロセスによって作成されることを許可しないでください。これらのバックアップから復元するシーケンスが失われるためです）。
各データベースの順次処理の例を次に示します。 しかし、本番環境では、一度に複数のバックアップを作成することにより、処理を並列化することが非常に可能です。 これはさまざまな方法で実行できます。 たとえば、次のストアドプロシージャを呼び出します。
非同期的にリクエストを呼び出すための手順
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
ここでは、エージェントタスクを動的に作成し、その後の実行と削除で非同期を実現しています。
次に、以前に作成したバックアップからデータベースを復元するための一般的なアルゴリズムを示します（別の環境またはテスト環境で）：
1）どのデータベースを復元する必要があるか、およびバックアップの場所を決定します
2）データベースを復元する
3）復元されたデータベースの整合性を確認します
以下は、完全バックアップからデータベースを復元するアルゴリズムの実装例です。 違いの手順については、手順は似ていますが、最初に完全バックアップが復元され、次に差分バックアップが復元されます。
復元する必要があるデータベースとバックアップの場所を判断するには、2つのテーブルを作成します。
DB回復セットアップテーブル
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
ここで、列の割り当ては[srv]。[BackupSettings]テーブルの列の割り当てに似ていますが、唯一の違いはフルパスがバックアップされず、リカバリに使用されることです。
回復用のデータベースファイルテーブル
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
このテーブルは、後続の転送のために復元されたデータベースの完全なファイル名を決定するために必要です（たとえば、[SourcePathRestore] = 'Logical file name' and [TargetPathRestore] = 'drive：\ ... \ Physical file name'、and [Ext] = 'ファイル拡張子 '。
実際、ここでは、次のクエリによってデータベースファイルの論理名を特定できます。
論理データベースファイル名の取得
RESTORE FILELISTONLY FROM DISK =':\...\ .BAK';
また、次のようにして、ファイル内のバックアップに関する情報を取得できます。
データベースのバックアップに関する情報を取得する
RESTORE HEADERONLY FROM DISK=':\...\ .BAK';
次に、完全なバックアップからデータベースを復元し、その後にデータの整合性チェックを行うストアドプロシージャの実装例を示します。
完全バックアップのデータベース回復手順
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
ここでは、フルバックアップを復元するかを決定するために、ファイル名が取得され、次のように形成されている：
<データベース名> _Full_backup_ <年> _ <nomer_mesyatsa_v_godu> _ <nomer_dnya_v_mesyatse>明博
フルバックアップからデータベースを復元するプロセスの自動化のために、上記で実装したストアドプロシージャの呼び出しを、Windowsタスクスケジューラ、エージェントのタスク、または他の利用可能なサービスに配置するだけです。
次のビューを使用して、最新のデータベースバックアップを表示できます。
最新のデータベースバックアップの提出
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;
結果
この記事では、あるサーバーで自動バックアッププロセスを実装し、その後別のサーバー（テストサーバーなど）で復元する例を紹介しました。
この方法により、バックアップの作成プロセスを自動化し、復元方法を使用してバックアップをチェックし、上記のプロセスを微調整することができます。
ソース：
» 講義5：完全復旧モデル
» バックアップ
» 復元
» バックアップセット
» CHECKDB
» SHRINKFILE
» sys.master_files
» sp_async_execute