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