thc_oO: MS SQL Server

ラベル MS SQL Server の投稿を表示しています。すべての投稿を表示

2012年2月9日木曜日

既存の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

2012年1月12日木曜日

10進数から16進数に変換するSQL

確認した環境は SQL Server2008

master.sys.fn_varbintohexsubstring(0,convert(varbinary,<任意の数値>),1,0)

fn_varbintohexsubstringはドキュメントレスなプロシージャのようでMSDNでの情報が無い。
プロシージャの仕様を確認するのには

sp_helptext 'fn_varbintohexsubstring'

SQL Serverのバージョンによって利用できるプロシージャ名が異なるので注意。

SQL7:master.dbo.xp_varbintohexstr
SQL2000:master.dbo.fn_varbintohexstr / master.dbo.fn_varbintohexsubstring
SQL2005:master.sys.fn_varbintohexstr / master.sys.fn_varbintohexsubstring

参考にさせて頂いたサイト：開発リソース/SQLServer/SHA1ハッシュを生成する方法 - isla-plata.org Wiki

最初、ぐぐって Stigma - in the public_enemy - [SQL] SQLServer - 10進数→16進数変換を参考にさせて頂こうとしたけど、このページで紹介されているやり方だと16進で2桁までしか対応していないようで、採用しませんでした。以下、比較

declare @var bigint;
set @var = 15
print 'convert dec to hex : ' + convert(varchar,@var)
print STUFF((master.dbo.fn_varbintohexstr(cast(cast(@var as bigint) as binary(1))) COLLATE Latin1_General_CI_AS_KS_WS ),1,2,'') 
print master.sys.fn_varbintohexsubstring(0,convert(varbinary,@var),1,0)
set @var = 511
print 'convert dec to hex : ' + convert(varchar,@var)
print STUFF((master.dbo.fn_varbintohexstr(cast(cast(@var as bigint) as binary(1))) COLLATE Latin1_General_CI_AS_KS_WS ),1,2,'') 
print master.sys.fn_varbintohexsubstring(0,convert(varbinary,@var),1,0)
set @var = 65535;   --0xFFFF
print 'convert dec to hex : ' + convert(varchar,@var)
print STUFF((master.dbo.fn_varbintohexstr(cast(cast(@var as bigint) as binary(1))) COLLATE Latin1_General_CI_AS_KS_WS ),1,2,'') 
print master.sys.fn_varbintohexsubstring(0,convert(varbinary,@var),1,0)
set @var = 65536;   --0x1 0000
print 'convert dec to hex : ' + convert(varchar,@var)
print STUFF((master.dbo.fn_varbintohexstr(cast(cast(@var as bigint) as binary(1))) COLLATE Latin1_General_CI_AS_KS_WS ),1,2,'') 
print master.sys.fn_varbintohexsubstring(0,convert(varbinary,@var),1,0)
set @var = 4294967295   --0xFFFF FFFF
print 'convert dec to hex : ' + convert(varchar,@var)
print STUFF((master.dbo.fn_varbintohexstr(cast(cast(@var as bigint) as binary(1))) COLLATE Latin1_General_CI_AS_KS_WS ),1,2,'') 
print master.sys.fn_varbintohexsubstring(0,convert(varbinary,@var),1,0)

2011年8月12日金曜日

行単位で持っているデータを列として出力するSQL

ひとつの id に対して複数の val を行単位で保有するテーブル (表１)
から、ひとつの id に対して複数の val を列単位で抽出 (表２) するSQLのメモ
表２の取得は２種類
　その１．列として出力する内容を固定で定義
　その２．列として出力する内容が可変でもOKなように

テスト用テーブル作成するSQL　と表１の取得

-- テスト用テーブル作成するSQL：
if exists (
    select * from tempdb.dbo.sysobjects
    where id = object_id('tempdb.dbo.#hoge')
    )
begin
     drop table #hoge
end
select * into #hoge from (
select 'a' id,'o' val union all
select 'a','x' union all
select 'a','-' union all
select 'a','+' union all
select 'a','/' union all
select 'b','o' union all
select 'c','x')t


-- 表１の内容を出力するSQL：
select * from #hoge

表２の内容を出力するSQL その１

-- 表２の内容を出力するSQL：
--　　列として出力する内容が固定
select id
    ,   max(case row_num when 1 then val else '' end ) val1
    ,   max(case row_num when 2 then val else '' end ) val2
    ,   max(case row_num when 3 then val else '' end ) val3
    ,   max(case row_num when 4 then val else '' end ) val4
    ,   max(case row_num when 5 then val else '' end ) val5
from (
    select id, val, row_number() over (partition by id order by id) row_num from #hoge
    ) a
group by id

表２の内容を出力するSQL その２

-- 表２の内容を出力するSQL：
--　　列として出力する内容が可変
declare @cntLoop int
    ,   @cntColumn int
    ,   @variableColFields as varchar(8000)
    ,   @variableColumnName as varchar(10)
set @cntLoop = 1
set @variableColFields = ''
set @variableColumnName = 'val'

select @cntColumn = max(rownum) from (
    select row_number() over (partition by id order by id) rownum
    from #hoge
    ) a

while @cntLoop <= @cntColumn
begin
    if @cntLoop > 1
    begin
        set @variableColFields = @variableColFields + ','
    end

    
    set @variableColFields = @variableColFields
        + ' max(case row_num when ' + convert(varchar,@cntLoop) 
        + ' then val else '''' end ) ' + @variableColumnName + convert(varchar,@cntLoop) + char(13) + char(10)
    set @cntLoop = @cntLoop + 1   
end


exec ('select id ,' + @variableColFields + '
from (
    select id, ' + @variableColumnName + ', row_number() over (partition by id order by id) row_num from #hoge
    ) a
group by id')