既存のDBをコピーして別DBを作る

既存のDBファイル（MDF,LDF）を元に、同一サーバ内にDBのコピーを別名で作成する為のSQL。
たまに使うし、忘れるしメモ。
MS SQL Server 2005, MS SQL Server 2008 で動作確認。

SQLとエクスプローラー上での操作が必要
1．SQL：@Action=1 で実行。移動元DBをデタッチ
2．エクスプローラ：MDF,LDFファイルをコピーして、移動先DB名でリネーム
3．SQL：@Action=2 で実行。移動元DBをアタッチ（元に戻す）
4．SQL：@Action=3 で実行。移動先DBを作成および、2で作成したファイルにアタッチ。
以上で作業完了。

その他メモ：
Windows2008で40GB弱のファイルをエクスプローラーのコピーでコピーすると30分以上かかる（14.4MB/秒）。
バックアップファイルがあるならバックアップファイルからリストアしたほうが早いかも。

:参考
SQL Server のデタッチとアタッチ機能を使用して SQL Server データベースを新しい場所に移動する方法

デタッチとアタッチを使用してデータベースを移動する方法 (Transact-SQL)

/*

#1. 移動元DB：デタッチ
#2. 移動元DB：アタッチ
#3. 移動先DBの作成
*/
DECLARE @Save_dir       VARCHAR(MAX)
    ,   @From_DBName    SYSNAME
    ,   @From_mdf       VARCHAR(MAX)
    ,   @From_ldf       VARCHAR(MAX)
    ,   @To_DBName      SYSNAME
    ,   @To_mdf         VARCHAR(MAX)
    ,   @To_ldf         VARCHAR(MAX)
    ,   @Action int;
/* 
設定値
　@Save_dir　：データ領域（MDF,LDF）が置かれるフォルダの絶対パスを指定
　@From_DBName　：移動元DBのカタログ名
　@To_DBName　：移動先DBのカタログ名
 */
SET @Save_dir = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\';
SET @From_DBName = '';
SET @To_DBName = '';
--SET @Action = 0;   --DB情報の参照
SET @Action = 1 --#1. 移動元DB：デタッチ
--SET @Action = 2 --#2. 移動元DB：アタッチ
--SET @Action = 3 --#3. 移動先DBの作成


SELECT CASE @Action WHEN 1 THEN '#1.移動元DB：デタッチ'
                    WHEN 2 THEN '#2.移動元、移動先DB：アタッチ'
                    WHEN 3 THEN '#3. 移動先DBの作成'
                    ELSE 'DB情報の参照' END

use master;
  
SET @From_mdf = @Save_dir + @From_DBName + '.mdf';
SET @From_ldf = @Save_dir + @From_DBName + '_log.ldf';
SET @To_mdf = @Save_dir + @To_DBName + '.mdf';
SET @To_ldf = @Save_dir + @To_DBName + '_log.ldf';

BEGIN TRY
    IF @Action = 1
    BEGIN
        EXEC sp_detach_db @From_DBName;
    END
    IF @Action = 2
    BEGIN
        EXEC sp_attach_db @From_DBName, @From_mdf, @From_ldf;
    END
    IF @Action = 3
    BEGIN
        EXEC ('CREATE DATABASE ' + @To_DBName + ' ON (FILENAME = [' + @To_mdf + ']),(FILENAME = [' + @To_ldf + '])
            FOR ATTACH' );
        -- 移動先の論理ファイル名を設定
        EXEC ( 'ALTER DATABASE ' + @To_DBName + ' MODIFY FILE (NAME = ' + @From_DBName + ', NEWNAME = ' + @To_DBName + ');' )
        EXEC ( 'ALTER DATABASE ' + @To_DBName + ' MODIFY FILE (NAME = ' + @From_DBName + '_log, NEWNAME = ' + @To_DBName + '_log);' )
    END

    DECLARE @SQLString NVARCHAR(max)
        ,   @DBName NVARCHAR(max);

    SET @DBName = @From_DBName;
    SET @SQLString = N'USE ' + @DBName + '
                       exec sp_helpfile';
    EXEC sp_executesql @SQLString;

    SET @DBName = @To_DBName;
    SET @SQLString = N'USE ' + @DBName + '
                       exec sp_helpfile';
    EXEC sp_executesql @SQLString;
END TRY
BEGIN CATCH
    SELECT  '### ERROR ####' ,
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() as ErrorState,
            ERROR_PROCEDURE() as ErrorProcedure,
            ERROR_LINE() as ErrorLine,
            ERROR_MESSAGE() as ErrorMessage;
END CATCH
go