こんにちは、キャスレーコンサルティング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
最後に
今回はデタッチとアタッチを簡単に実行するコマンドを紹介させていただきました。
複数データベースのデタッチ、アタッチを駆使することで、
環境構築や運用時の作業を効率化できるのではないでしょうか。
次回は別のコマンドを紹介したいと思いますので、ぜひご活用ください。