SQLServer2014の復旧モデルとバックアップについて

Question

SQLServerのバックアップとリカバリーについて教えてください。
主に収集した情報についての正誤になると思います。

####環境
OS：WindowsServer2012R2
DB：SQLServer2014 Enterprise、SQLServer2014 Express

####背景
0. SQLServerが作成しているトランザクションログが、増大しデータインポートにタイムアウトが発生。
0. トランザクションログは、自動拡張10%単位を固定値に変更することで解決可能。
0. 毎日完全バックアップを取っている（1週間で過去は消す）。
0. バックアップは同じサーバーに保存している。

####質問
0. 完全復旧モデルでのリカバリー方法について
解釈した要約）
1)バックアップされたDBを基準とする
2)バックアップされたログを充てていく
上記を踏まえて）
・バックアップした時点以降のデータが不要な場合は、トランザクションログは不要でしょうか。
例）
・再実行してバックアップを復旧できる場合など。
0. トランザクションログの切り捨てについて
解釈した要約）
1)トランザクションログのバックアップを取る
2)トランザクションログを圧縮して容量を空ける
上記を踏まえて）
・トランザクションログを限定して切り捨てることは可能でしょうか。
例）
・1週間分は残して過去分は自動的に切り捨てるなど。
0. 復旧モデルが単純の場合のログ
解釈した要約）
・トランザクションログは自動的に上書きされる
上記を踏まえて）
1)リカバリーには全く使えないのでしょうか。
2)使える場合についてログの履歴はどの程度保障されるのでしょうか。
0. 復旧モデルの変更した場合のログ
1)完全⇒単純にした場合は、トランザクションログは消失してしまいますか。
2)単純⇒完全にした場合は、その時点以降のトランザクションログが作成されるのでしょうか。
0. バックアップデータの自動移動
・ジョブを作成して自動でファイル移動（別サーバー）をさせたいが、SQLServer（Enterprise、Express）上では可能でしょうか。
その他）
1)バックアップ自体はEnterpriseにて、エージェントを使用して定期的に作成しています。
2)難しいようなら、外部バッチを作成してファイル編集や移動を行うことで対応は可能だと思います。

####求めている目標
0. バックアップは毎日とるため、トランザクションログを過去分からずっと保持はしたくない。
0. 更新頻度は夜間に集中しているため、その時間帯のトランザクションログの容量追加によるタイムアウトエラーを無くしたい。
0. 回答いただける内容は、1.～5.の単体でも構いません。

####追記
SQLServer2014 Enterpriseを利用されてる方はあまり多くないかもしれませんので、Editionが違う場合で運用されているパターンなどでも構いません。参考にさせていただいて解決を目指せればと思います。

また、情報不足でありましたら、どのような情報が必要か教えていただけると幸いです。
よろしくお願いいたします。

####エージェントでのエラー内容

1. 設定情報

SQL Serverエージェント ⇒ ジョブ ⇒ 新しいジョブ ⇒ 全般（名前：test、所有者：sa） ⇒ ステップ
⇒ 新規作成（ステップ名：1、種類：オペレーティングシステム、実行するアカウント名：SQL Serverエージェントサービスのアカウント）

1. コマンド内容

copy "F:(中略)\test.bak" "F:(中略)\test_1.bak"
move /y "F:(中略)_.bak" (net2)
(net2は別なサーバー）

1. 実行時のエラーログ

09/01/2017 17:43:28,test1,エラー,,(net)(DB名),test,,,ジョブは失敗しました。 ジョブは ユーザー sa によって起動されました。最終実行ステップはステップ 1 (1) でした。.,00:00:00,0,0,,,,0
09/01/2017 17:43:28,test,エラー,1,(net)(DB名),test,1,,次のユーザーとして実行: NT Service\SQLAgent$CLED。ステップは失敗しました。,00:00:00,0,0,,,,0
09/01/2017 17:43:28,test,実行中,1,(net)(DB名),test,1,,アクセスが拒否されました。,00:00:00,0,0,,,,0

昨日から解析中ですが、何とかわかり始めましたが、やはり別なサーバーへ移すところで権限に問題がありそうです。
難しそうならタスクスケジューラからバッチを起動して、移すことにグレードダウンすることにします。

Answer 1

「求めている目標」を実現するモデルが単純モデルとなると思います。

これを踏まえて、

復旧モデルが単純の場合のログ
1)リカバリーには全く使えないのでしょうか。

使えません。
復旧モデルが単純の場合のログは、
SQL Server の自動復旧機能のために利用されるものです。

バックアップデータの自動移動

SQL Server Agent でも、CmdExecで実行できますが、
デフォルトでは SQL Server Agent Service のサービスアカウントの
権限で実行されますので、権限には注意してください。

Comments

[FitD]

koguma98さん

目標を考慮した質問に対するご回答ありがとうございます。
> > 復旧モデルが単純の場合のログ
> > 1)リカバリーには全く使えないのでしょうか。
> 使えません。
ログ自体は生成され、自動的に切り捨てられる。であれば、残っているログは使えるのではないか、と思いましたが、やはり使えないのですね。

> バックアップデータの自動移動
> SQL Server Agent でも、CmdExecで実行できますが、
> デフォルトでは SQL Server Agent Service のサービスアカウントの
> 権限で実行されますので、権限には注意してください。
エージェントの内部で作成した場合は権限に注意しなくてはならないのですね。
どうしてもうまくいかない場合、別途バッチを起動させる必要がありそうですね。

[FitD]

途中経過を報告させていただきます。
現在のところ復旧モデルを完全　⇒　単純にすることになりました。
完全モデルとの違いが明確でないので、実際にトランザクションログを使って復旧できるかできないかの差だけなのかがわかっていません。
この点を現在調査中です。

[FitD]

Koguma98さんがおっしゃったとおり、エージェント内部のcmdを現在うまく動作させることができておりません。
権限の問題であればどのように対応すればよいのか調査中です。
詳細は本文に追記します。

[koguma98]

「(net2)\」というのはUNCパスでしょうか？
move の前に net use で接続してはどうでしょう？
ex) net use (net2) パスワード /user:ユーザー

[FitD]

koguma98さん
返信遅くなりすみません、別対応で不在にしてたため確認が遅くなりました。
今現在の状況としては、Enterpriseのエージェントの機能を使わない方向にシフトしています。

それは別件として、moveの前に　net useで接続に関しても確認してみます。
また、(net2)\というのは、バックアップデータを保管する別なサーバのUNC（PC名）です。

Answer 2

結果を記入し忘れてました。

結局のところ、毎日のバックアップのオペレーションは、OSのタスクスケジューラ機能でバッチを起動し、バックアップファイルをコピー、ftpで転送、転送にミスったらVBSでメール転送ということに落ち着きました。

当初の質問とはかなり回り道をした解決方法となりましたが、これにて解決といたします。