none
データのバックアップの方法について教えてください。 RRS feed

  • 質問

  • 環境は以下の通りです。
     Windows7 
     VisualStudio 2013 C# .NET
     SQLServer 2014


    現在2つのサーバ(SV1とSV2)があります。
    関連する2つのテーブルがそれぞれのサーバーにあり
    最新のデータのみバックアップをとりたいのですが

    CodeTable 列 : [Code]・・・[]
    NameTable 列 : [Code][Name]・・・[]

     

    [処理1-1]
    ConnectionString="Data Source=SV2"にて
    TRUNCATE TABLE [DBase].[dbo].[CodeTable];

    [処理1-2]
    ConnectionString="Data Source=SV1"にて
    INSERT INTO [SV2].[DBase].[dbo].[CodeTable]
    SELECT *
    FROM [DBase].[dbo].[CodeTable]");

    [処理2-1]
    ConnectionString="Data Source=SV2"にて
    TRUNCATE TABLE [DBase].[dbo].[NameTable]

    [処理2-2]
    ConnectionString="Data Source=SV1"にて
    INSERT INTO [SV2].[DBase].[dbo].[NameTable]
    SELECT *
    FROM [DBase].[dbo].[NameTable]");

    にてコピーができるのですが、
    データ数が多い(4万件ぐらい)ため1つ目の処理で
    数分かかってしまいます。

    その間に、他からアクセスされ、CodeとName
    が追加・削除などの変更がされ
    コピー後のCodeTableにコードがないのに
    NameTableに名前が登録されているということになります。

    これを防ぐために最初に2つのテーブルにロックを
    かければいいのかもしれませんが、
    そうすると、処理1、処理2で10分以上
    アクセスできなくなるのも困ったものですが、
    それはそれでしかたがないので
    「現在バックアップ中のため処理できない」
    とか表示するしかないかと思っています。

    このような場合。どうするのがベストな方法なのでしょうか
    教えてください。

    2014年10月1日 0:13

回答

  • それはバックアップの問題なのでしょうか? バックアップでなくてもNameTableに書き込んだ後、CodeTableに書き込む前にアプリケーションが落ちた場合、データが不正になるのではありませんか? 落ちなくてもタイミング悪くアクセスした場合にも不正なデータを取得してしまうのではありませんか?

    一般的には書き込む際にトランザクションを使い、複数の書き込みを1つにまとめます。そうすると書き込み前か全ての書き込み後のどちらかにしかアクセスできなくなります。その場合、バックアップであっても不正な状態で読み込むことがなくなります。

    そうではなくバックアップで解決したい場合は、DB内でテンポラリテーブルにコピーし、テンポラリテーブルの内容をゆっくりバックアップする方法があります。この場合、テンポラリテーブルへのコピー時間だけをロックすれば済みます。

    • 回答としてマーク 星 睦美 2014年10月24日 5:13
    2014年10月1日 1:46
  • 何が許容できる/出来ないのか、や何を重要視する/しないによるので、そのあたりの基準の話が無い中でベストは?と聞かれても難しいところがありますが、たとえばデータ取得元のテーブルの Snapshot を作成し、Snapshot からデータコピーするといった方法などを検討されてはいかがでしょうか?
    Snapshot については以下を参照してください。
    http://technet.microsoft.com/ja-jp/library/ms175158(v=sql.120).aspx

    MCITP(Database Developer/Database Administrator)

    • 回答としてマーク 星 睦美 2014年10月24日 5:13
    2014年10月1日 1:56
  • レプリケーション等の機能を使わず、SQLで処理するのであれば、insert, update, delete文を生成して実行した方が効率が良いでしょう。例えば以下のような感じになりますが、update文は全ての列を比較し、全ての列を更新するという効率の悪いものになっていますので、もしtimestamp列が用意できれば、それで比較されると良いでしょう。全ての列の更新を止めたければ、ストアドプロシージャやC#などの言語でも良いですが、効率の良いSQLを生成するようにロジックを組む必要があるでしょう。
    ロックに関しては考慮されているようですので、適切に行って下さい。

    select 'insert into dbo.Table_backup (Code, ColA, ColB, ColC) values (' +
                convert(nvarchar(50), o.Code) +  ',''' + o.ColA + ''',''' + o.ColB + ''',''' + o.ColC + ''')'
    from dbo.Table_original o left outer join dbo.Table_backup b on o.Code = b.Code
    where b.code is null
    
    select 'delete dbo.Table_backup where Code = ' + convert(nvarchar(50), b.Code)
    from dbo.Table_original o right outer join dbo.Table_backup b on o.Code = b.Code
    where o.Code is null
    
    select 'update dbo.Table_backup set ColA=''' + o.ColA + ''', ColB=''' + o.ColB + ''', ColC=''' + o.ColC +
             ''' where Code = ' + convert(nvarchar(50), b.Code)
    from dbo.Table_original o inner join dbo.Table_backup b on o.Code = b.Code
    where o.ColA <> b.colA or o.ColB <> b.colB or o.ColC <> b.colC
    #難しいSQLではありませんので、特に説明は要らないと思います。簡単な動作テストを行っていますが、実際に運用される場合には十分に確認して下さい。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • 編集済み trapemiya 2014年10月1日 2:32 誤字修正
    • 回答としてマーク 星 睦美 2014年10月24日 5:13
    2014年10月1日 2:30

すべての返信

  • それはバックアップの問題なのでしょうか? バックアップでなくてもNameTableに書き込んだ後、CodeTableに書き込む前にアプリケーションが落ちた場合、データが不正になるのではありませんか? 落ちなくてもタイミング悪くアクセスした場合にも不正なデータを取得してしまうのではありませんか?

    一般的には書き込む際にトランザクションを使い、複数の書き込みを1つにまとめます。そうすると書き込み前か全ての書き込み後のどちらかにしかアクセスできなくなります。その場合、バックアップであっても不正な状態で読み込むことがなくなります。

    そうではなくバックアップで解決したい場合は、DB内でテンポラリテーブルにコピーし、テンポラリテーブルの内容をゆっくりバックアップする方法があります。この場合、テンポラリテーブルへのコピー時間だけをロックすれば済みます。

    • 回答としてマーク 星 睦美 2014年10月24日 5:13
    2014年10月1日 1:46
  • 何が許容できる/出来ないのか、や何を重要視する/しないによるので、そのあたりの基準の話が無い中でベストは?と聞かれても難しいところがありますが、たとえばデータ取得元のテーブルの Snapshot を作成し、Snapshot からデータコピーするといった方法などを検討されてはいかがでしょうか?
    Snapshot については以下を参照してください。
    http://technet.microsoft.com/ja-jp/library/ms175158(v=sql.120).aspx

    MCITP(Database Developer/Database Administrator)

    • 回答としてマーク 星 睦美 2014年10月24日 5:13
    2014年10月1日 1:56
  • レプリケーション等の機能を使わず、SQLで処理するのであれば、insert, update, delete文を生成して実行した方が効率が良いでしょう。例えば以下のような感じになりますが、update文は全ての列を比較し、全ての列を更新するという効率の悪いものになっていますので、もしtimestamp列が用意できれば、それで比較されると良いでしょう。全ての列の更新を止めたければ、ストアドプロシージャやC#などの言語でも良いですが、効率の良いSQLを生成するようにロジックを組む必要があるでしょう。
    ロックに関しては考慮されているようですので、適切に行って下さい。

    select 'insert into dbo.Table_backup (Code, ColA, ColB, ColC) values (' +
                convert(nvarchar(50), o.Code) +  ',''' + o.ColA + ''',''' + o.ColB + ''',''' + o.ColC + ''')'
    from dbo.Table_original o left outer join dbo.Table_backup b on o.Code = b.Code
    where b.code is null
    
    select 'delete dbo.Table_backup where Code = ' + convert(nvarchar(50), b.Code)
    from dbo.Table_original o right outer join dbo.Table_backup b on o.Code = b.Code
    where o.Code is null
    
    select 'update dbo.Table_backup set ColA=''' + o.ColA + ''', ColB=''' + o.ColB + ''', ColC=''' + o.ColC +
             ''' where Code = ' + convert(nvarchar(50), b.Code)
    from dbo.Table_original o inner join dbo.Table_backup b on o.Code = b.Code
    where o.ColA <> b.colA or o.ColB <> b.colB or o.ColC <> b.colC
    #難しいSQLではありませんので、特に説明は要らないと思います。簡単な動作テストを行っていますが、実際に運用される場合には十分に確認して下さい。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • 編集済み trapemiya 2014年10月1日 2:32 誤字修正
    • 回答としてマーク 星 睦美 2014年10月24日 5:13
    2014年10月1日 2:30