Скрипт архивации баз данных Microsoft SQL Server с полной моделью восстановления

В продолжение своей предыдущей статьи по архивации БД MSSQL и негативному отклику в связи с отсутствием возможности архивации логов транзакций, работаю я теперь в компании, где понадобилось автоматизировать этот момент для баз в том числе с полной моделью восстановления.

Скрип работает универсально для баз с различной моделью восстановления, в начале скрипта добавлены настройки для относительно гибкого формирования расписания. Скрипт можно поставить с SQL Agent и удобным интервалом (у меня, например, 1 раз час), первый запуск в сутках будет проверять, надо создавать или нет полную или разностную копию сегодня и далее в течении дня для БД с полной моделью восстановления будут создаваться бэкапы лога журнала транзакций.

-- пути до бэкапов  declare @FullPath varchar(500) = 'D:\SQLBackup' declare @DiffPath varchar(500) = 'D:\SQLBackup' declare @LogPath varchar(500) = 'D:\SQLBackup'  -- архивируемые и исключенные из архивации БД  declare @IncludeBase varchar(500) = 'master, msdb' -- если не пусто, то только эти минус исключенные, если пусто то все минус исключенные declare @ExcludeBase varchar(500) = 'tempdb'   -- настроки для полного бэкапа -- минимум хранить дней declare @MinFullDay int = 181 -- выполнять каждые дней declare @MinFullExecDay int = 7   -- настроки для разностного бэкапа -- минимум хранить дней declare @MinDiffDay int = 181 -- выполнять каждые дней declare @MinDiffExecDay int = 1  -- настроки для бэкапа журнала -- минимум хранить дней declare @MinLogDay int = 35    -- как часто создавать master (дни) declare @MinDayMaster int = 1  -- сколько хранить информацию об архивах в msdb (дни) --declare @DayHistory int = @MinFullDay + 30   -- включим сжатие  EXEC sp_configure 'show advanced options', 1;  EXEC sp_configure 'backup compression default', 1; RECONFIGURE WITH OVERRIDE; -- включим xp_cmdshell (нужно для удаления старых бэкапов) EXEC sp_configure 'show advanced options', 1;  EXEC sp_configure 'xp_cmdshell', 1;  RECONFIGURE WITH OVERRIDE;    declare @tempcmd varchar(500) ='' declare @tempname varchar(500) =''  -- создание путей set @tempcmd= 'md '+@FullPath exec xp_cmdshell @tempcmd, no_output set @tempcmd= 'md '+@DiffPath exec xp_cmdshell @tempcmd, no_output set @tempcmd= 'md '+@LogPath exec xp_cmdshell @tempcmd, no_output    -- определяем список БД для архивации declare @BaseListIncl table (name varchar(200)) declare @BaseListExcl table (name varchar(200))  if @IncludeBase=''  insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE'  else while len(@IncludeBase)>0 begin if CHARINDEX (',',@IncludeBase)>0 begin insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and name = SUBSTRING(@IncludeBase,1, CHARINDEX (',',@IncludeBase)-1) set @IncludeBase=LTRIM(RTRIM(SUBSTRING(@IncludeBase,CHARINDEX (',',@IncludeBase)+1, LEN(@IncludeBase)))) end else begin insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and name = @IncludeBase set @IncludeBase='' end  end  if @ExcludeBase<>''  while len(@ExcludeBase)>0 begin if CHARINDEX (',',@ExcludeBase)>0 begin insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and name = SUBSTRING(@ExcludeBase,1, CHARINDEX (',',@ExcludeBase)-1) set @ExcludeBase=LTRIM(RTRIM(SUBSTRING(@ExcludeBase,CHARINDEX (',',@ExcludeBase)+1, LEN(@ExcludeBase)))) end else begin insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and name = @ExcludeBase set @ExcludeBase='' end  end  -- итоговый список БД для бэкапа delete from @BaseListIncl  where name in (select name from @BaseListExcl)  declare BaseList cursor for select name from @BaseListIncl   declare @BaseName varchar(500) ='' declare @BackupType varchar(10) = ''  open BaseList fetch next from BaseList into @BaseName    while @@FETCH_STATUS = 0   begin        -- получаем тип бэкапа, который надо сделать (для мастера специальные условия, он не может ни каких кроме FULL) SET @BackupType =        ( CASE   WHEN @BaseName = 'master' AND (EXISTS (SELECT * FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='D' and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinDayMaster)) THEN '' WHEN @BaseName = 'master' AND (NOT EXISTS (SELECT * FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='D' and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinDayMaster)) THEN 'FULL' WHEN (NOT EXISTS (SELECT * FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='D' and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinFullExecDay))  THEN 'FULL' WHEN (NOT EXISTS (SELECT * FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE IN ('I','D') and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinDiffExecDay))  THEN 'DIFF' WHEN (EXISTS (select * from sys.databases as a where a.recovery_model_desc = 'FULL' and a.name = @BaseName))  THEN 'LOG' ELSE '' END   )    IF @BackupType <> '' begin   --создаем папку для БД SET @tempcmd =  (CASE WHEN @BackupType='FULL' OR @BaseName='master' THEN 'md '+@FullPath+'\'+@BaseName WHEN @BackupType='DIFF' THEN 'md '+@DiffPath+'\'+@BaseName ELSE 'md '+@LogPath+'\'+@BaseName END) exec xp_cmdshell @tempcmd, no_output    if @BackupType='FULL' begin -- full backup set @tempname = @FullPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_FULL.BAK' backup database @BaseName to disk = @tempname end else if @BackupType='DIFF' begin -- diff backup set @tempname = @DiffPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_DIFF.BAK' backup database @BaseName to disk =@tempname with differential end else  begin -- log backup set @tempname = @LogPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_LOG.TRN' BACKUP LOG @BaseName to disk =@tempname  end   -- удаляем истекшие бэкапы declare @delpath varchar(500)='' declare delbackup cursor for  SELECT mf.physical_device_name FROM   msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='D' and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) > @MinFullDay and is_copy_only = 0  union all SELECT mf.physical_device_name FROM   msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='I' and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) > @MinDiffDay and is_copy_only = 0  union all SELECT mf.physical_device_name FROM   msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='L' and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) > @MinLogDay and is_copy_only = 0     open delbackup  fetch next from delbackup into @delpath    while @@FETCH_STATUS = 0  begin set @tempcmd= 'del /f /q '+QUOTENAME(@delpath,'"') exec xp_cmdshell @tempcmd, no_output   fetch next from delbackup into @delpath end  close delbackup  deallocate delbackup  end  fetch next from BaseList into @BaseName       end    close BaseList   deallocate BaseList    -- чистим в MSDB информацию о старых архивах --declare @oldest DATETIME  --SET @oldest = DATEADD(DAY, -@DayHistory, GETDATE())  --EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest

Так же добавлю скрипт для создания бэкапов «длительного хранения». Создаются с опцией ONLY_COPY и не участвуют в общей цепочке восстановления, я обычно использую раз — два в месяц для снятия полной копии или по необходимости

-- ========== СОЗДАЕМ ТОЛЬКО ПОЛНЫЕ БЭКАПЫ С ОПЦИЕЙ ONLY_COPY (не учавствуют в общей цепочке)  -- пути до бэкапов (для типовых) declare @Path varchar(500) = 'I:\SQLBackupOnlyCopy'  -- архивируемые и исключенные из архивации БД  declare @IncludeBase varchar(500) = 'buh, zup' -- если не пусто, то только эти минус исключенные, если пусто то все минус исключенные declare @ExcludeBase varchar(500) = 'tempdb'   -- включим сжатие  EXEC sp_configure 'show advanced options', 1;  EXEC sp_configure 'backup compression default', 1; RECONFIGURE WITH OVERRIDE; -- включим xp_cmdshell (нужно для удаления старых бэкапов) EXEC sp_configure 'show advanced options', 1;  EXEC sp_configure 'xp_cmdshell', 1;  RECONFIGURE WITH OVERRIDE;    declare @tempcmd varchar(500) ='' declare @tempname varchar(500) =''  -- создание путей set @tempcmd= 'md '+@Path exec xp_cmdshell @tempcmd, no_output  -- определяем список БД для архивации declare @BaseListIncl table (name varchar(200)) declare @BaseListExcl table (name varchar(200))  if @IncludeBase=''  insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE'  else while len(@IncludeBase)>0 begin if CHARINDEX (',',@IncludeBase)>0 begin insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and name = SUBSTRING(@IncludeBase,1, CHARINDEX (',',@IncludeBase)-1) set @IncludeBase=LTRIM(RTRIM(SUBSTRING(@IncludeBase,CHARINDEX (',',@IncludeBase)+1, LEN(@IncludeBase)))) end else begin insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and name = @IncludeBase set @IncludeBase='' end  end  if @ExcludeBase<>''  while len(@ExcludeBase)>0 begin if CHARINDEX (',',@ExcludeBase)>0 begin insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and name = SUBSTRING(@ExcludeBase,1, CHARINDEX (',',@ExcludeBase)-1) set @ExcludeBase=LTRIM(RTRIM(SUBSTRING(@ExcludeBase,CHARINDEX (',',@ExcludeBase)+1, LEN(@ExcludeBase)))) end else begin insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and name = @ExcludeBase set @ExcludeBase='' end  end  -- итоговый список БД для бэкапа delete from @BaseListIncl  where name in (select name from @BaseListExcl)  declare BaseList cursor for select name from @BaseListIncl   declare @BaseName varchar(500) ='' declare @BackupType varchar(10) = ''  open BaseList fetch next from BaseList into @BaseName    while @@FETCH_STATUS = 0   begin        --создаем папку для БД SET @tempcmd = 'md '+@Path+'\'+@BaseName exec xp_cmdshell @tempcmd, no_output  --создаем архив set @tempname = @Path+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_FULL_CopyOnly.BAK' backup database @BaseName to disk = @tempname WITH COPY_ONLY   fetch next from BaseList into @BaseName        end    close BaseList   deallocate BaseList