こんにちは、キャスレーコンサルティングSI(システム・インテグレーション)部の安井です。

今回はSQL Serverで使える便利なコマンドを紹介したいと思います。

・sp_detach_db
・sp_attach_db

sp_detach_dbコマンドはDBをデタッチするコマンドで、
sp_attach_dbコマンドはDBのアタッチするコマンドです。
コマンド名のままですね。。。

デタッチしたファイルを別のサーバーに移動してアタッチすることでDBの移動もできますし、
DBのコピーやアップグレードにも利用できます。

ひとまず使い方を説明します。

sp_detatch_db

EXEC sp_detach_db データベース名

sp_detach_dbの引数にデタッチしたいデータベース名を指定することで、 簡単にデタッチすることが出来ます。

このコマンドはカーソル文を使用することで、 複数のデータベースをデタッチすることも可能です。

↓こんな感じです。

DECLARE @db_name varchar(100)
DECLARE cur1 CURSOR FOR
SELECT
    name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN cur1
FETCH NEXT FROM cur1 INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC sp_detach_db @db_name

    FETCH NEXT FROM cur1 INTO @db_name
END

CLOSE cur1
DEALLOCATE cur1

sp_attach_db

EXEC sp_attach_db データベース名, MDFファイル名, LDFファイル名

sp_attach_dbの引数にアタッチ後のデータベース名、パス付MDFファイル名、
パス付LDFファイル名を指定することで、簡単にアタッチできます。

このコマンドはデタッチコマンドと違い実際のファイル名を指定しなければならないため、
複数のデータベースファイルをアタッチすることが出来ないのがちょっと難点です。

ただし、先ほどの複数デタッチクエリにちょっと細工をすると、複数アタッチすることも出来ます。

ちょっとした細工とは、データベース情報を格納する一時テーブルを作成し、
カーソルでループするときに、データベース名、MDFファイル、LDFファイルを一時テーブルに格納します。

↓ちょっとした細工をしたデタッチコマンド

IF EXISTS (SELECT * FROM tempdb.sys.tables WHERE name LIKE '##DB_MNG_TBL%')
BEGIN
    DROP TABLE ##DB_MNG_TBL
END

CREATE TABLE ##DB_MNG_TBL (
    dbname VARCHAR(400),
    mdfname VARCHAR(MAX),
    ldfname VARCHAR(MAX)
);

DECLARE @sql NVARCHAR(MAX)
DECLARE @db_name VARCHAR(400)
DECLARE @mdf_name VARCHAR(MAX)
DECLARE @ldf_name VARCHAR(MAX)

DECLARE cur1 CURSOR FOR
SELECT
    name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN cur1

FETCH NEXT FROM cur1
INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @sql = 'SELECT @file_name = physical_name FROM ' + @db_name + '.sys.database_files WHERE type_desc = @type'
    EXEC sp_executesql @sql, N'@file_name NVARCHAR(max) OUTPUT, @type VARCHAR(4)', @file_name = @mdf_name OUTPUT, @type = 'ROWS'
    EXEC sp_executesql @sql, N'@file_name NVARCHAR(max) OUTPUT, @type VARCHAR(4)', @file_name = @ldf_name OUTPUT, @type = 'LOG'

    INSERT INTO ##DB_MNG_TBL VALUES(@db_name, @mdf_name, @ldf_name)

    EXEC sp_detach_db @db_name

    FETCH NEXT FROM cur1
    INTO @db_name
END

CLOSE cur1
DEALLOCATE cur1

アタッチ時には先ほどの一時テーブルよりデータベース情報を取得し、
その値を元にアタッチを行います。

↓アタッチ時はこんな感じです。

DECLARE @sql nvarchar(max)
DECLARE @db_name VARCHAR(400)
DECLARE @mdf_name VARCHAR(MAX)
DECLARE @ldf_name VARCHAR(MAX)

DECLARE cur1 CURSOR FOR
SELECT
    dbname,
    mdfname,
    ldfname
FROM ##DB_MNG_TBL

OPEN cur1

FETCH NEXT FROM cur1
INTO @db_name, @mdf_name, @ldf_name

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC sp_attach_db @db_name, @mdf_name, @ldf_name

    FETCH NEXT FROM cur1
    INTO @db_name, @mdf_name, @ldf_name
END

CLOSE cur1
DEALLOCATE cur1

DROP TABLE ##DB_MNG_TBL

最後に

今回はデタッチとアタッチを簡単に実行するコマンドを紹介させていただきました。

複数データベースのデタッチ、アタッチを駆使することで、
環境構築や運用時の作業を効率化できるのではないでしょうか。

次回は別のコマンドを紹介したいと思いますので、ぜひご活用ください。