t-sql - backup database と restore database で変数を使う

backup database や restore database で DB名のところに変数を指定できます。

DB名やテーブル名などでは変数が使えないと思っていたのでちょっとした発見でした。ということでクエリを残しておきます。

backup のサンプルクエリ

declare @dbName nvarchar(max) = N'Test';
declare @backup nvarchar(max) = N'C:\Temp\Test.bak';

backup database @dbName
to disk = @backup;

DB名に変数を使っています。またバックアップ先にも変数が使えます。

restore のサンプルクエリ

declare @dbName nvarchar(max) = N'Test';
declare @backup nvarchar(max) = N'C:\Temp\Test.bak';

restore database @dbName
from disk = @backup;

backup とほほ同じですね。

またDBを移行するときなどで、リストア時にファイルパスを変更する場合があると思います。 そのときは move オプションを指定しますが、その論理名とファイルパスにも変数を指定することができます。

declare @dbName nvarchar(max) = N'Test';
declare @backup nvarchar(max) = N'C:\Temp\Test.bak';

-- 論理名
declare @mdfName nvarchar(max) = N'Test';
declare @ldfName nvarchar(max) = N'Test_log';

-- ファイルの保存先
declare @mdf nvarchar(max) = N'/* データフォルダ */\Test.mdf';
declare @ldf nvarchar(max) = N'/* データフォルダ */\Test.ldf';

restore database @dbName
from disk = @backup
with
    move @mdfName to @mdf,  -- mdfファイルパスを変更
    move @ldfName to @ldf;  -- ldfファイルパスを変更

カーソルを使いつつ複数DBをバックアップ・リストアみたいな使い方ができそうかなと思います。(SQL を文字列として組み立てて、sp_executesql でも同じことができるとは思いますが・・・。)