SE の雑記

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

クエリベースでデータベース単位のバックアップ世代を管理

leave a comment

SQL Server のメンテナンスプランでは、[メンテナンス クリーンアップ タスク] を使用することで、バックアップの世代を管理することができます。
image

このタスクですがファイル名またはファイルの拡張子に基づいての削除となるため、特定のデータベースのバックアップを削除するということは難しいです。
バックアップの拡張子にデータベース名を付与 (<バックアップ名>.<データベース名>.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 には既定のバックアップディレクトリがあります。

image

このバックアップディレクトリについてはレジストリに格納されていますので、レジストリにアクセスができれば値を取得することが可能です。

SQL Server ではクエリからレジストリにアクセスすることが可能ですので以下のようなクエリで既定のバックアップディレクトリの設定を取得できます。

DECLARE @BackupDirectory nvarchar(255)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory',  @BackupDirectory OUTPUT
SELECT @BackupDirectory

 

image

ディレクトリが取得できたらあとはディレクトリに格納されているファイルの情報を取得します。

ファイルの情報の取得は [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) となるようです

今回は全ディレクトリのファイル / ディレクトリを取得しています。

image

これで特定のディレクトリに格納されているファイルの情報が取得できました。

それでは上記のディレクトリから [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

 

上記のクエリを実行すると以下のような情報が取得できます。

image

[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 のコマンドを実行することもできますが、セキュリティの脆弱性につながることもありますので、今回は有効にすることなくファイル操作をする方向でクエリを書いてみました。

# 一時的にも有効にしないことを目指して書いてみました。

Written by masayuki.ozawa

3月 9th, 2014 at 7:03 pm

Posted in SQL Server

Tagged with

Leave a Reply

*