SQL Server のメンテナンスプランでは、[メンテナンス クリーンアップ タスク] を使用することで、バックアップの世代を管理することができます。
このタスクですがファイル名またはファイルの拡張子に基づいての削除となるため、特定のデータベースのバックアップを削除するということは難しいです。
バックアップの拡張子にデータベース名を付与 (<バックアップ名>.<データベース名>.bak) して、拡張子を <データベース名>.bak のものを削除対象とすればある程度は制御できるのですが。
今回の投稿では、クエリベースでデータベースのバックアップ世代を管理する方法をまとめてみたいと思います。
メンテナンスクリーンアップタスクでファイルを削除する際には、[master.dbo.xp_delete_file] という拡張ストアドプロシージャが実行されています。
一定の期間を経過した特定の拡張子のファイルを削除する場合には、
EXECUTE master.dbo.xp_delete_file 0,N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLBackup',N'.bak',N'2014-02-09T17:55:46'
削除対象のファイルを固定して削除する場合には、
EXECUTE master.dbo.xp_delete_file 0,N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLBackupTEST_20140205202114_full.bak'
というように実行してファイルの削除を行います。
バックアップ取得先のディレクトリに格納されているファイルがわかれば、ある程度クエリでも制御ができるようになります。
SQL Server には既定のバックアップディレクトリがあります。
このバックアップディレクトリについてはレジストリに格納されていますので、レジストリにアクセスができれば値を取得することが可能です。
SQL Server ではクエリからレジストリにアクセスすることが可能ですので以下のようなクエリで既定のバックアップディレクトリの設定を取得できます。
DECLARE @BackupDirectory nvarchar(255) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT SELECT @BackupDirectory
ディレクトリが取得できたらあとはディレクトリに格納されているファイルの情報を取得します。
ファイルの情報の取得は [master.sys.xp_dirtree] を使用します。
この拡張ストアドプロシージャでは特定のディレクトリ内のファイルの情報を取得することができます。
詳細な情報については以下の記事が詳しいです。
How to Use xp_dirtree to List All Files in a Folder
先ほどのクエリと合わせて、以下のようなクエリを実行してみます。
DECLARE @BackupDirectory nvarchar(255) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT SELECT @BackupDirectory EXEC master.sys.xp_dirtree @BackupDirectory, 0,1
そうすると以下のような情報が取得できます。
第一引数が深さ / 第二引数はファイルとディレクトリを含める (1), ディレクトリのみ (0) となるようです
今回は全ディレクトリのファイル / ディレクトリを取得しています。
これで特定のディレクトリに格納されているファイルの情報が取得できました。
それでは上記のディレクトリから [TEST] データベースのバックアップのファイルを取得してみたいと思います。
今回はバックアップファイル名は <データベース名_> の形式で固定されているものとします。
DECLARE @BackupDirectory nvarchar(255) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL DROP TABLE #DirectoryTree; CREATE TABLE #DirectoryTree (subdirectory nvarchar(255),depth int,isfile bit) INSERT #DirectoryTree (subdirectory,depth,isfile) EXEC master.sys.xp_dirtree @BackupDirectory, 1,1 SELECT subdirectory FROM #DirectoryTree WHERE subdirectory LIKE N'TEST[_]%' DROP TABLE #DirectoryTree
[xp_dirtree] の実行結果をテーブルに格納し、そこから LIKE 句を使用して、先頭がデータベース名のレコードを取得します。
ファイル名に日付を入れている場合はソート順を調整することで [最新] [古いもの] の順番を制御することができますので、ここで世代の調整ができるようになります。
最終的に作成したクエリが以下になります。
SET NOCOUNT ON GO DECLARE @BackupDirectory nvarchar(255) DECLARE @subdirectory nvarchar(255) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL DROP TABLE #DirectoryTree; CREATE TABLE #DirectoryTree (subdirectory nvarchar(255),depth int,isfile bit) INSERT #DirectoryTree (subdirectory,depth,isfile) EXEC master.sys.xp_dirtree @BackupDirectory, 1,1 DECLARE BackupFile CURSOR FOR SELECT TOP 1 subdirectory FROM #DirectoryTree WHERE subdirectory LIKE N'TEST[_]%' ORDER BY 1 ASC OPEN BackupFile FETCH NEXT FROM BackupFile INTO @subdirectory WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE @delfile nvarchar(512) = (SELECT @BackupDirectory + '' + @subdirectory) EXECUTE master.dbo.xp_delete_file 0, @delfile FETCH NEXT FROM BackupFile INTO @subdirectory END CLOSE BackupFile DEALLOCATE BackupFile DROP TABLE #DirectoryTree
[xp_delete_file] を実行することでファイルの削除をしていますが、その時には [xp_dirtree] で取得した結果を使用し、固定のファイル名で削除をしています。
今回は TEST というデータベース名で固定していますが、[sys.databases] の実行結果を使用すれば全データベースを対象として、処理を実行することも可能です。
SELECT name from sys.databases WHERE state = 0
xp_cmdshell を使用することで、OS のコマンドを実行することもできますが、セキュリティの脆弱性につながることもありますので、今回は有効にすることなくファイル操作をする方向でクエリを書いてみました。
# 一時的にも有効にしないことを目指して書いてみました。