組み込みツールを使用してデータベースをバックアップおよび復元するための自動プロセスの実装例

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 

 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 

 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 

 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 

 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 

 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 

 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 

 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;