SQL Serverに存在するシステムデータベースを除いたすべてのデータベースを一括で任意のフォルダに完全バックアップするT-SQLのスクリプトを紹介します。
概要
SQL Serverに存在する複数のデータベースを一つずつ手作業でバックアップするのはデータベースの数が多い場合、非常に面倒です。
ということで、ボタン一発でシステムデータベースを除く全てのデータベースを指定した任意のフォルダにバックアップするT-SQLのスクリプトを紹介します。
T-SQLを使用した一括バックアップスクリプト
T-SQLの一括バックアップスクリプトです。
SQL Server Management Studioから対象のSQL Serverに接続して、[新しいクエリ]画面に以下のスクリプトを貼り付けて実行するだけです。
DECLARE @DatabaseName VARCHAR(255)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @BackupPath VARCHAR(255)
DECLARE @BackupFileName VARCHAR(255)
SET @BackupPath = 'C:\work\'
SET @BackupFileName = @BackupPath + @DatabaseName + '_FullBackup_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', ''), ' ', ''), ':', '') + '.bak'
BACKUP DATABASE @DatabaseName TO DISK = @BackupFileName
FETCH NEXT FROM db_cursor INTO @DatabaseName
END
CLOSE db_cursor
DEALLOCATE db_cursor
バックアップを保管する任意のフォルダの指定とバックアップファイル名は、19、20行目になります。
@BackupPath = 'C:\work\'
SET @BackupFileName = @BackupPath + @DatabaseName + '_FullBackup_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', ''), ' ', ''), ':', '') + '.bak'上記のスクリプトでは、保管時のバックアップファイル名は、デーベース名+"_FullBackup_"+バックアップ日時を表す日付文字列 + 拡張子".bak"となっています。
デーベース名だけにしたければ、以下のように修正してください。
SET @BackupFileName = @BackupPath + @DatabaseName + '.bak'注意
バックアップファイルの保管先に指定するフォルダは、接続先SQL Serverのフォルダになります。
T-SQLを実行したPCのフォルダではないことに注意してください。
実行方法
SQL Server Management Studioを起動して対象のSQL Serverに接続します。
ツールバーから[新しいクエリ]ボタンをクリックし、エディタ画面を表示します。
T-SQLのスクリプトを貼り付けます。
ツールバーの[実行]ボタンをクリックして実行します。
エラーメッセージ等表示されず、バックアップ対象分の完了メッセージ「正常に処理されました」が表示されて、完了時刻が表示されれば終了です。
バックアップ保管先に指定したディレクトリを確認すれば以下のようにデータベースのバックアップファイルが作成されているはずです。
まとめ
SQL Serverのシステムデータベースを除く全てのデータベースを任意のフォルダに一括でバックアップするT-SQLを紹介しました。
PowerShellで同じ処理をするスクリプトも紹介したかったのですが、Microsoft.SqlServer.SqlManagementObjectsのインストールが何故か上手くいかなかったのでとりあえずT-SQLだけの紹介でした。
PowerShellは時間のあるときにまたトライしたいと思います。
コメント