私の個人的な趣向によりますが、私の部署でもBlogに".Text"を使っています。使い出してからは、特にバックアップはせずに過ぎておりましたが、休暇中に結構コンテンツが溜まってきたので、バックアップをすることにしました。

日々のバックアップは、シンプルに、bcpを使って必要なデータだけをファイルに書き出すように設定。
あわせて、別のSQL Serverにオブジェクトごとバックアップしようと思い、Database Copy Wizardを使ってみることにしました。

まず、シンプルに「デタッチ」->「アタッチ」でのコピーは問題無く成功。では、と思い、次にSMOオプション(SQL管理オブジェクトを使用する方法）を使ってオンラインでコピーができるかどうか試してみました。

コピーウィザードを使うと、SSISのパッケージが作成され、それがSQL Agentのジョブに登録されるわけですが、予想通り失敗....。

よく話題に上がる、「Integration ServicesやVisual Studio上ではSSISパッケージは実行できるけれど、SQL Agent上のジョブでは失敗する」という状況に。SQL Agent上では、プロキシアカウントのコンテキストでジョブが実行されるので、権限設定を行って、再度挑戦。?

で、色々と試してみたのですが、サンプルデータベースのAdventureWorksだと、うまくオンラインでコピーが出来るのに、対象のデータベースだとコピーに失敗してしまうという状況になってしまいました。

しかも、コピー作業の途中までは上手く行っていて、コピー先のデータベースは作成されているものの、オブジェクトの転送が途中で失敗しているという状況でした。ただ、SQL Agentのジョブの履歴には、「プロキシアカウントでのパッケージの実行に失敗しました」とのメッセージしか表示されないので、原因が良く判らず....。

思考錯誤した挙句、プロキシアカウントで直接Integration Servicesにログインし、SSISパッケージを実行してみることに。で、やっとのことでエラーの原因がわかりました。

  1. コピー先のSQL ServerへのログインはOK。
  2. コピー先となるデータベースの作成はOK。（この時点ではオブジェクトは転送されていない）
  3. コピー元のデータベースの参照はOK。 オブジェクトの転送が開始される。
    • コピー元のオブジェクトが参照できるので、あとはつつがなくオブジェクト転送が出来ると思ったら....
  4. パッケージ（ジョブ）を実行しているプロキシアカウントと同じユーザが、すでにコピー元のデータベースにあった場合、CREATE USER 「自分」という状況になって、失敗しているらしい。
    • エラー: ERROR; errorCode=-1073548784 description=クエリ “CREATE USER [DOMAIN\sqlagent] FORLOGIN [DOMAIN\sqlagent] WITH DEFAULT_SCHEMA=[xxxx]“の実行が次のエラーで失敗しました: ”ログインは別のユーザ名でアカウントがあります。”。考えられるエラーの理由.....(省略)

そもそもSQL Agent用のプロキシアカウントは、sysadminロールを与えているので、個別のデータベースに明示的にユーザとして登録していなくても、もちろんデータベースにアクセスできます。でも、以前SQL Agentの設定をしたときに、わざわざデータベースにユーザ登録をしていたのでした。成功していたAdventureWorksには、このプロキシアカウントはユーザとして登録はされていなかったので、問題無かったのでしょうか.....。

結局、コピー元のデータベースから、プロキシアカウントのユーザを削除してみました。

その後、再度コピーのパッケージを実行すると、サラリと成功して処理が完了...。Integration Servicesからの実行はOKとなったので、次にSQL Agentのジョブのほうから、プロキシアカウントのコンテキストで実行しても、問題なくコピーが完了となりました。

「SQL AgentとSSISパッケージの実行の辺りは、たぶん自分もハマルだろうなあ.....」とは思っていたのですが、プロキシの動作は問題なく、さらにその先のことで詰まっていた、という結果でした。

SQL Agentのジョブの履歴からは、ここまでは判らなかったので、気が付くのに時間がかかってしまいました。（ただし、この方法が正解なのかどうかも怪しいのですが）

あいかわらずヘナチョコな日々でお恥ずかしい限りです。なかなか難しいですね(^^;