SQL Server Express管理

SQL Server Expressの概要

無償のSQL Serverである Expressエディションは、以前は MSDE（Microsoft SQL Server 2000 Desktop Engine）と呼ばれていて、「管理ツールのないデータベース」でしたが、SQL Server 2005/2008では 管理ツールがあり、無償で使えました。管理ツール付のSQL Server 2008 Expressは、「SQL Server 2008 Express with Tools」からダウンロードできました。管理ツールだけは、「SQL Server 2008 Management Studio Express」からダウンロードできました。管理ツールは SQL Server 2005/2008では 「Management Studio」という名前でした。インストール後、[スタート]メニューから Management Studioを起動すると、データベース接続の画面が表示されます。

・サーバーの種類：データベースエンジン
・サーバー名：(ローカルマシン名)￥SQLEXPRESS
・認証：Windows認証
を選択、または入力し、[接続]すれば データベースにつながります。サーバー名（本当は SQL Serverのインスタンス名）は「マシン名￥インスタンス名」の形式です。SQL Server Expressをインストールすると 既定で SQLEXPRESSというインスタンス名になります。

サーバー名（本当は SQL Serverのインスタンス名）は「マシン名￥インスタンス名」の形式です。SQL Server Expressをインストールすると 既定で SQLEXPRESSというインスタンス名になります。インスタンス名は、[コントロールパネル] - [管理ツール] - [サービス]で、名前が「SQL Server」のサービスを探すと、確認できます。認証は Windows認証とSQL Server認証の２種類があります。 SQL Server Expressをインストールすると 既定で Windows認証になります。

データベースサーバーに接続されると Management Studioのメイン画面が表示され、左側のオブジェクトエクスプローラに 接続したデータベースサーバーが表示されます。

データベースサーバーの下には
・データベース
・セキュリティ
・サーバー オブジェクト
・レプリケーション
・管理
のカテゴリ(フォルダマーク)があります。

ログイン

SQL Serverの認証には Windows認証とSQL Server認証の２種類があります。Windows認証の場合、Windowsユーザーを使用して認証がおこなわれます。SQL Server認証の場合、SQL Serverで登録した ログインを使用して認証がおこなわれます。ActiveDirectoryなどのWindowsユーザー管理がしっかりした環境であれば Windows認証の方が 管理が楽ですが、 単純にSQL Serverを使うだけの環境（ワークグループ環境、ActiveDirectory環境外にしたい場合）などでは、SQL Server認証が楽です。SQL Server認証では 古くから sa（System Administratorの略）ログインという「既定の管理者ログイン」があります。管理者用の最強の権限を持っているにも関わらず、saを使って パスワード無しで 何でもありの状況で SQL Serverを使う人達もいまして、 「それはセキュリティ的にどうなのよ？」ということで．．．SQL Server 2008では saログインはあるけどログインが無効の状態が既定値となっています。

saを使うためには...
・Management Studioのオブジェクトエクスプローラで [セキュリティ]、[ログイン]を開く
・「sa」をダブルクリック
・ログインのプロパティ画面の左側で [状態]をクリック
・右側中央のログインを[有効]にして [OK]をクリック

本来は データベースごとに 必要最低限の権限を持ったログインを (sa以外に)作成して、そのログインを使う方がよろしいのですが．．．(^-^;

バックアップ（手動）

無償とはいえ SQL Server Expressもデータベースですから データが蓄積していくと あふれてしまいます。また、マシンの故障や ハードディスクトラブルなどが起きた時のことを考えると、定期的にバックアップしておいた方がよろしいかと。そんな訳で(?) SQL Server Expressのバックアップです。バックアップは 以下の手順でおこないます。
・Management Studioのオブジェクトエクスプローラで [データベース]を開く
・バックアップするデータベースを右クリックして、[タスク]、[バックアップ]をクリック
・データベースのバックアップ画面で、ソース、バックアップセット、バックアップ先などを設定して [OK]をクリック

ソースには
・データベース
・バックアップの種類
などがありますが データベースは そのままでOKです。

バックアップの種類には 完全と差分の２種類があり、完全は データベースそのまま全部、差分は 以前バックアップしてからの変更箇所のみです。データベースのサイズが小さければ 完全で とっておけばOKだと思います。

バックアップセットには
・名前
・有効期限
などがありますが そのままでOKです。

バックアップ先には．．．
既定では SQL Server Expressのデータベースが存在するフォルダ(Program Filesフォルダ配下)に (データベース名).bakというファイルを作成するようになっていますが 「おいおいっ！」「こらこら」っていう感じですね。
そもそもバックアップは データベース(ファイル)の故障、マシン・ハードディスクの故障に備えるためのものなのに データベースと同じ場所にバックアップして どうするの？
．．．という訳で
・別マシン（ファイルサーバーなど）にバックアップするか
・別ハードディスクにバックアップするか
・とりあえず同じところにバックアップして 別メディア（CD-Rなど）に退避するか
してください。

データベースのバックアップ画面の左側ペインで [オプション]を選択すると メディアへの上書き、信頼性(チェックなど)、圧縮などのオプションも指定できます。

バックアップ（自動）

Management Studioで バックアップをする手順については 説明しましたが、管理を楽にするためには 自動、定期的なバックアップですね。しかし SQL Server Expressには エージェント(SQL Server Agent。バックグランドで動作し ジョブを実行するプログラム)が入っていませんし、Management Studioにも 自動バックアップのメニューは準備されていません。しかし しかし・・・Windowsの「タスク スケジューラ」という便利なスケジューラを使えば 定期的にバッチファイル、スクリプトファイルなどが実行できます。必要なものは
・「タスク スケジューラ」に登録するバッチファイル（例：D:￥Work￥SS-BACKUP.BAT）
・バッチファイルから呼び出すデータベーススクリプト（例：D:￥Work￥SS-SCRIPT.SQL）
の２ファイルです。

(注) ファイル名の￥記号は全角になっていますので、コピペする場合は 半角に変更してください。

まずは バッチファイル（例：D:￥Work￥SS-BACKUP.BAT）を メモ帳などのテキストエディタで作ります。
マシン名が MY-PCで Windows認証の場合
SQLCMD -S MY-PC￥SQLEXPRESS -E -i D:￥Work￥SS-SCRIPT.SQL > D:￥Work￥SS-BACKUP.LOG

マシン名が MY-PCで SQL Server認証、saユーザー、パスワード sqlpass の場合
SQLCMD -S MY-PC￥SQLEXPRESS -U sa -P sqlpass -i D:￥Work￥SS-SCRIPT.SQL > D:￥Work￥SS-BACKUP.LOG

SQLCMDは SQL Server Command Line Toolで SQL Serverをインストールすると ついてくるコマンドラインツールです。コマンドプロンプトで 「SQLCMD /?」と入力し [Enter]を押すと オプションが表示されます。

-S server

SQL Server名

-E

Windows認証の場合に指定

-i inputfile

入力ファイル(ここでは スクリプトファイル)

-U login id

SQL Server認証のログイン

-P password

SQL Server認証のパスワード

バッチファイルの最後の方にある 「> D:￥Work￥SS-BACKUP.LOG」は、SQLCMDの実行結果を SS-BACKUP.LOGに保存するという意味です。次に データベーススクリプトを メモ帳などのテキストエディタで作ります。
データベース名 TESTDB、バックアップ先 D:￥Backup￥TESTDB.BAKの場合
BACKUP DATABASE TESTDB TO DISK='D:￥Backup￥TESTDB.BAK' WITH INIT GO

データベーススクリプトでは T-SQLというSQL Serverを管理するための コマンドを使用して データベースをバックアップしています。

ここまで できたら コマンドプロンプトで バッチファイル（例：D:￥Work￥SS-BACKUP.BAT）を実行して 正しくバックアップされるか、実行結果（SS-BACKUP.LOG）に何が入るかなどを確認します。コマンドプロンプトで バックアップができることを確認後、Windows タスクスケジューラに バッチファイルを登録します。
・[スタート]メニューの([コントロールパネル]、)[管理ツール]、[タスク スケジューラ]をクリック

・タスクスケジューラの右側ペインで [基本タスクの作成]をクリック
・基本タスクの作成ウィザード 基本タスクの作成で 名前、説明を入力し、[次へ]をクリック

・基本タスクの作成ウィザード トリガーで 起動間隔を選択し、[次へ]をクリック

・基本タスクの作成ウィザード トリガー(毎日)で 時間、間隔などを選択し、[次へ]をクリック（※この画面は トリガーを 毎日にした場合です。毎週にすると曜日など、毎月にすると日付などが選択できます。）

・基本タスクの作成ウィザード 操作で 「プログラムの開始」を選択し、[次へ]をクリック

・基本タスクの作成ウィザード 操作 プログラムの開始で、バッチファイル（例：D:￥Work￥SS-BACKUP.BAT）を選択し、[次へ]をクリック

・基本タスクの作成ウィザード 概要で、[完了]をクリック

以上で タスク スケジューラへの登録は完了です。

実際に タスクが動作した後、正しくバックアップされるか、実行結果（SS-BACKUP.LOG）が入っているか、タスクスケジューラ実行結果がどうなるかを確認してください。

デタッチ・アタッチ

SQL Serverが実行されているときは 物理的なデータベースファイルのコピーなどができません。そんな時は デタッチ・アタッチすれば エクスプローラで ファイルコピーできます。デタッチは 以下の手順でおこないます。
・Management Studioのオブジェクトエクスプローラで [データベース]を開く
・デタッチするデータベースを右クリックして、[タスク]、[デタッチ]をクリック
・データベースのデタッチ画面で、[OK]をクリック

デタッチが完了すると Management Studioのオブジェクトエクスプローラ [データベース]から デタッチしたデータベースがいなくなります。この状態で エクスプローラなどで データベースファイル（例：C:￥Program Files￥Microsoft SQL Server￥MSSQL10.SQLEXPRESS￥MSSQL￥DATA￥TESTDB.mdf）がコピーできます。ちなみに データベースファイルは (データベース名).mdf というファイルで、トランザクションログファイルは (データベース名)_log.ldfというファイルです。コピーなどの作業が終わったら アタッチします。

アタッチは 以下の手順でおこないます。
・Management Studioのオブジェクトエクスプローラで [データベース]を開く
・[データベース]を右クリックして、[タスク]、[アタッチ]をクリック
・データベースのアタッチ画面で、[追加]をクリックして データベースファイル(*.mdf)を選択し、[OK]をクリック

アタッチが完了すると Management Studioのオブジェクトエクスプローラ [データベース]に アタッチしたデータベースが表示されます。