SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

データベース作成時に自動的にバックアップを取得する

leave a comment

差分バックアップとログバックアップを使用した運用をする場合、起点となる完全バックアップが必要となります。
週一で完全バックアップ / 残りの曜日はフルバックアップというようなバックアップタクトがよくあるパターンかと思います。

このような運用をする場合の起点となるバックアップを自動的に取得するトリガーを作成してみたいと思います。

以下のようなトリガーを作成することで、データベース作成時に自動的にフルバックアップを取得することができます。

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Auto Backup Trigger] ON ALL SERVER WITH EXECUTE AS 'sa'
 FOR CREATE_DATABASE AS BEGIN
   SET NOCOUNT ON
   DECLARE @data XML
   DECLARE @sql NVARCHAR(MAX)
   DECLARE @name SYSNAME
   DECLARE @login SYSNAME
   DECLARE @error_message NVARCHAR(MAX)
   DECLARE @error_severity INT
   DECLARE @error_state INT
   SELECT @data = EVENTDATA()
   SELECT @name = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME'),
    @login = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')
   BEGIN TRY
	  COMMIT TRAN
      DECLARE @BKUPDIR nvarchar(255)
      EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory',  @BKUPDIR OUTPUT
	  DECLARE @date nvarchar(14) = (SELECT convert (varchar(10) , getdate(),112) + convert (varchar(2),(datepart(hh,GETDATE()))) + convert (varchar(2),(datepart(mi,GETDATE()))) + + convert (varchar(2),(datepart(ss,GETDATE()))))
      SET @sql = N'BACKUP DATABASE ' + @name + N' TO DISK = '''
		  + @BKUPDIR +'' +@name + '_' + @date + '_full.bak''' + N' WITH FORMAT,COMPRESSION, MEDIANAME = '''
		  + @name + '_' + @date + '_full_bakup'',' + 'NAME = ''Full Backup of '
		  + @name +'_' + @date +N''''
      EXEC (@sql)AS LOGIN = 'sa'
	  BEGIN TRAN
   END TRY
   BEGIN CATCH
	  SELECT @@TRANCOUNT
	  IF @@TRANCOUNT > 0
	  BEGIN
		ROLLBACK TRAN
	  END
      BEGIN TRAN
	  SET @error_message = ERROR_MESSAGE()
	  SET @error_severity = ERROR_SEVERITY()
	  SET @error_state = ERROR_STATE()
      RAISERROR(@error_message, @error_severity,@error_state) WITH LOG
   END CATCH
END
GO

このトリガーを作成した状態でデータベースを作成すると、既定のバックアップフォルダに自動的にバックアップが取得されます。

既定のバックアップフォルダであれば、レジストリからバックアップディレクトリを取得する必要はないのですが、サンプルとして取得するようにしています。

このようなトリガーを設定しておくと SQL Server の管理とアプリケーション管理者が異なっていても自動的にバックアップが取得されますので、新規の DB が作成されても管理の手間がなくなるかと。

Share

Written by Masayuki.Ozawa

2月 5th, 2014 at 8:32 pm

Posted in SQL Server

Tagged with

Leave a Reply