arkuznetsov · October 12, 2018 11:57
diff --git a/MS_SQL_backup_logs.sql b/MS_SQL_backup_logs.sql
 USE [master]
 GO

 SET NOCOUNT ON

 DECLARE
      @db SYSNAME
    , @sql NVARCHAR(MAX)
    , @can_compress BIT
    , @WithValidate BIT = 1
    , @path NVARCHAR(4000)
    , @name SYSNAME
    , @include_time BIT

 DECLARE @include_db TABLE ([db] nvarchar(255))
 DECLARE @exclude_db AS TABLE ([db] nvarchar(255))

 -- список баз, исключенных из обработки
 INSERT INTO @exclude_db ([db]) VALUES ('master');
 INSERT INTO @exclude_db ([db]) VALUES ('model');
 INSERT INTO @exclude_db ([db]) VALUES ('msdb');
 INSERT INTO @exclude_db ([db]) VALUES ('tempdb');

 -- список баз для обработки
 --INSERT INTO @include_db ([db]) VALUES ('mybase'); 
 INSERT INTO @include_db ([db]) VALUES ('DEV_AKuznetsov_EvtHUB_BIT');

 --SET @path = '\\pub\backup' -- можно задать свой путь дл¤ бекапа
 IF @path IS NULL -- либо писать в папку дл¤ бекапов указанную по умолчанию
    EXEC [master].dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'Software\Microsoft\MSSQLServer\MSSQLServer',
            N'BackupDirectory', @path OUTPUT, 'no_output'

 SET @can_compress = ISNULL(CAST((
    SELECT value
    FROM sys.configurations
    WHERE name = 'backup compression default') AS BIT), 0)

 DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT d.name
    FROM sys.databases d
    WHERE d.[state] = 0
        AND d.name NOT IN (SELECT db FROM @exclude_db) -- базы для которых не надо делать бекапов
 		AND (d.name IN (SELECT db FROM @include_db) OR (SELECT TOP 1 db FROM @include_db) IS NULL)
 OPEN cur

 FETCH NEXT FROM cur INTO @db

 WHILE @@FETCH_STATUS = 0 BEGIN

    IF DB_ID(@db) IS NULL BEGIN
        PRINT N'База ' + @db + N' не найдена'
    END
 	ELSE IF DATABASEPROPERTYEX(@db, 'Status') != 'ONLINE' BEGIN
        PRINT N'База ' + @db + N' находится в режиме ' + CONVERT(NVARCHAR(10), DATABASEPROPERTYEX(@db, 'Status'))
    END
 	ELSE IF DATABASEPROPERTYEX(@db, 'Recovery') != 'FULL' BEGIN
 	    PRINT N'Режим восстановления базы ' + @db + N' - ' + CONVERT(NVARCHAR(10), DATABASEPROPERTYEX(@db, 'Recovery'))
    END
    ELSE BEGIN

        BEGIN TRY

            SET @name = @path + '\' + @db + '_' + CONVERT(CHAR(8), GETDATE(), 112) + REPLACE(CONVERT(CHAR(8), GETDATE(), 108), ':', '') + '.trn'
            SET @sql = '
                BACKUP LOG ' + QUOTENAME(@db) + '
                TO DISK = ''' + @name + ''' WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD' + 
                CASE WHEN @can_compress = 1 THEN ', COMPRESSION' ELSE '' END +
 				CASE WHEN @WithValidate = 1 THEN
 					'
 					DECLARE @backupSetId as int
 					SELECT @backupSetId = position
 					FROM msdb..backupset
 					WHERE database_name = N''' + @db + '''
 						AND backup_set_id = (SELECT max(backup_set_id) FROM msdb..backupset WHERE database_name = N''' + @db + ''' )

 					IF @backupSetId IS NULL BEGIN
 						raiserror(N''Ошибка верификации. сведения о резервном копировании для базы данных "' + @db + '" не найдены.'', 16, 1)
 					END
 					RESTORE VERIFYONLY FROM  DISK = ''' + @name + ''' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND'
 				ELSE '' END
            PRINT @sql
            EXEC sys.sp_executesql @sql

        END TRY
        BEGIN CATCH
 	        PRINT N'Ошибка создания резервной копии базы ' + @db + N': ' + ERROR_MESSAGE()
        END CATCH

    END

    FETCH NEXT FROM cur INTO @db
 END

 CLOSE cur
 DEALLOCATE cur
	USE [master]
	GO

	SET NOCOUNT ON

	DECLARE
	@db SYSNAME
	, @sql NVARCHAR(MAX)
	, @can_compress BIT
	, @WithValidate BIT = 1
	, @path NVARCHAR(4000)
	, @name SYSNAME
	, @include_time BIT

	DECLARE @include_db TABLE ([db] nvarchar(255))
	DECLARE @exclude_db AS TABLE ([db] nvarchar(255))

	-- список баз, исключенных из обработки
	INSERT INTO @exclude_db ([db]) VALUES ('master');
	INSERT INTO @exclude_db ([db]) VALUES ('model');
	INSERT INTO @exclude_db ([db]) VALUES ('msdb');
	INSERT INTO @exclude_db ([db]) VALUES ('tempdb');

	-- список баз для обработки
	--INSERT INTO @include_db ([db]) VALUES ('mybase');
	INSERT INTO @include_db ([db]) VALUES ('DEV_AKuznetsov_EvtHUB_BIT');

	--SET @path = '\\pub\backup' -- можно задать свой путь дл¤ бекапа
	IF @path IS NULL -- либо писать в папку дл¤ бекапов указанную по умолчанию
	EXEC [master].dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'BackupDirectory', @path OUTPUT, 'no_output'

	SET @can_compress = ISNULL(CAST((
	SELECT value
	FROM sys.configurations
	WHERE name = 'backup compression default') AS BIT), 0)

	DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
	SELECT d.name
	FROM sys.databases d
	WHERE d.[state] = 0
	AND d.name NOT IN (SELECT db FROM @exclude_db) -- базы для которых не надо делать бекапов
	AND (d.name IN (SELECT db FROM @include_db) OR (SELECT TOP 1 db FROM @include_db) IS NULL)
	OPEN cur

	FETCH NEXT FROM cur INTO @db

	WHILE @@FETCH_STATUS = 0 BEGIN

	IF DB_ID(@db) IS NULL BEGIN
	PRINT N'База ' + @db + N' не найдена'
	END
	ELSE IF DATABASEPROPERTYEX(@db, 'Status') != 'ONLINE' BEGIN
	PRINT N'База ' + @db + N' находится в режиме ' + CONVERT(NVARCHAR(10), DATABASEPROPERTYEX(@db, 'Status'))
	END
	ELSE IF DATABASEPROPERTYEX(@db, 'Recovery') != 'FULL' BEGIN
	PRINT N'Режим восстановления базы ' + @db + N' - ' + CONVERT(NVARCHAR(10), DATABASEPROPERTYEX(@db, 'Recovery'))
	END
	ELSE BEGIN

	BEGIN TRY

	SET @name = @path + '\' + @db + '_' + CONVERT(CHAR(8), GETDATE(), 112) + REPLACE(CONVERT(CHAR(8), GETDATE(), 108), ':', '') + '.trn'
	SET @sql = '
	BACKUP LOG ' + QUOTENAME(@db) + '
	TO DISK = ''' + @name + ''' WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD' +
	CASE WHEN @can_compress = 1 THEN ', COMPRESSION' ELSE '' END +
	CASE WHEN @WithValidate = 1 THEN
	'
	DECLARE @backupSetId as int
	SELECT @backupSetId = position
	FROM msdb..backupset
	WHERE database_name = N''' + @db + '''
	AND backup_set_id = (SELECT max(backup_set_id) FROM msdb..backupset WHERE database_name = N''' + @db + ''' )

	IF @backupSetId IS NULL BEGIN
	raiserror(N''Ошибка верификации. сведения о резервном копировании для базы данных "' + @db + '" не найдены.'', 16, 1)
	END
	RESTORE VERIFYONLY FROM DISK = ''' + @name + ''' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND'
	ELSE '' END
	PRINT @sql
	EXEC sys.sp_executesql @sql

	END TRY
	BEGIN CATCH
	PRINT N'Ошибка создания резервной копии базы ' + @db + N': ' + ERROR_MESSAGE()
	END CATCH

	END

	FETCH NEXT FROM cur INTO @db
	END

	CLOSE cur
	DEALLOCATE cur