差分バックアップとログバックアップを使用した運用をする場合、起点となる完全バックアップが必要となります。
週一で完全バックアップ / 残りの曜日はフルバックアップというようなバックアップタクトがよくあるパターンかと思います。
このような運用をする場合の起点となるバックアップを自動的に取得するトリガーを作成してみたいと思います。
以下のようなトリガーを作成することで、データベース作成時に自動的にフルバックアップを取得することができます。
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 が作成されても管理の手間がなくなるかと。