Masa's blog

_ Microsoft SQL Server儂(わし)的解釈によるメモ(sqlcmd編)

Microsoft SQL ServerをCUIでコントロールする為にsqlcmdコマンドが用意されている。SQL Server Management Studioを使えばGUIで操作できるが、やはりコマンドラインで操りたい...。

なぜなら、

• 操作の内容を記録に残しやすい。
• 操作の伝承がしやすい。
• 記録の再利用がしやすい。

などの理由があげられる。

例によって、各種ウェブページの切り貼り、再編集。

当然、無保証。

階層構造(あくまで個人的な解釈)

サーバ/インスタンス(SQLExpress)/データベース(master)/スキーマ(dbo)/テーブル

括弧内はデフォルト値。

データ型

int

• 整数データを格納するデータ型
• 記憶域サイズ：64bits
• 範囲：-2^31 (-2,147,483,648) 〜 2^31 - 1 (2,147,483,647)

decimal

• 固定長桁数、固定長小数桁数を持つ数値データを格納するデータ型
• 範囲：桁数最大38桁(標準セットアップでは28桁)

money

• 通貨を格納するデータ型
• 記憶域サイズ：64bits
• 範囲：-2^63(-922,337,203,685,477.5808)〜2^63 - 1(+922,337,203,685,477.5807)

float

• 浮動小数点数値を格納するデータ型
• 記憶域サイズ：64bits、128bits
• 範囲：- 1.79E + 308 〜1.79E + 308

char, nchar

• 8000文字以内の固定長の文字列を格納するデータ型。UNICODEで格納する場合はncharを使用する(4000文字以内)

varchar, nvarchar

• 8000文字以内の可変長の文字列を格納するデータ型。UNICODEで格納する場合はnvarcharを使用する(4000文字以内)

text, ntext

• 約20億文字以内の可変長の文字列を格納するデータ型。UNICODEで格納する場合はntextを使用する(約10億文字以内)

datetime

• 日付を格納するデータ型
• 範囲：1753 年 1 月 1 日〜9999 年 12 月 31 日
• 精度：3.33ミリ秒

sqlcmdのヘルプ

C:\Documents and Settings\m-ito>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 10.0.1600.22 NT INTEL X86
Copyright (C) Microsoft Corporation.  All rights reserved.

使用法: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

データベース作成

masterデータベースには重要なシステム情報が保存されるので、ユーザデータは別データベースを作成して、そちらに格納するのが好ましい。

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> CREATE DATABASE testdb
2> GO

データベース削除

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> DROP DATABASE testdb
2> GO

データベースの指定

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE testdb
2> GO

スキーマ作成

スキーマてなんじゃ？。データベースは複数のスキーマを含む器であり、スキーマは複数のテーブルを含む器である。具体的な使い方としては、同じ性格のテーブルをグループ化して管理しやすくしたり、グループ化することによりセキュリティを高めたりという事ではないかと想像する。

ちなみに、dboというスキーマがあらかじめ用意されており、明示的に指定しない場合はdbo内にテーブルが作成される。

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> use testdb
2> GO
3> CREATE SCHEMA testschema
2> GO

スキーマ削除

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> use testdb
2> GO
3> DROP SCHEMA testschema
2> GO

テーブル作成

スキーマを省略した場合はdboという名称のスキーマ内に作成される。

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> use testdb
3> CREATE TABLE testschema.testtable (id int)
2> GO

テーブル削除

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> use testdb
3> DROP TABLE testschema.testtable
2> GO

ログイン作成(SQL Server認証)

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE master
2> CREATE LOGIN testuser WITH PASSWORD = 'TESTUSER-PASSWORD'
3> GO

ログイン削除

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE master
2> DROP LOGIN testuser
3> GO

パスワードの変更

sqlcmd -U testuser -P CURRENT-PASSWORD -Z NEW-PASSWORD -S localhost\SQLEXPRESS

ユーザー作成

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE testdb
2> CREATE USER testuser FOR LOGIN testuser
3> GO

ユーザー削除

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE testdb
2> DROP USER testuser
3> GO

アクセス権の付与(管理者)

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE testdb
2> GRANT CONTROL ON DATABASE::testdb TO testuser WITH GRANT OPTION
3> GO

アクセス権の付与(ユーザ)

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE testdb
2> GRANT SELECT,INSERT,UPDATE,DELETE ON testtable TO testuser
3> GO

アクセス権の削除

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE testdb
2> REVOKE SELECT,INSERT,UPDATE,DELETE ON testtable FROM testuser
3> GO

接続

sqlcmd
1> :CONNECT localhost\SQLEXpress -U sa -P SA-PASSWORD

ヘルプを表示

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> :HELP

コマンド取消(GO実行前)

1> :RESET

ユーザ一覧

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE master
2> select name from syslogins
3> GO

データベース一覧

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> select name from sys.databases
2> GO

テーブル一覧

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> select name from sys.tables
2> GO

スキーマ一覧

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> select name from sys.schemas
2> GO

スキーマ、テーブル一覧

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> select SCHEMA_NAME(schema_id),name from sys.tables
2> GO

テーブル構造を見る

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> sp_columns 'testtable'
2> GO

バックアップモデル

単純復旧モデル : バックアップはフルバックアップと差分バックアップで運用する。ログ領域は自動的に削除される。(リストアに利用できない)。

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> ALTER DATABASE testdb SET RECOVERY SIMPLE
2> GO

完全復旧モデル : バックアップはフルバックアップと差分バックアップとログバックアップで運用する。ログ領域はバックアップを取るまで削除されない。

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> ALTER DATABASE testdb SET RECOVERY FULL
2> GO

現在設定されている復旧モデルの表示。

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'testdb'
2> GO

バックアップ(フル)

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> BACKUP DATABASE testdb TO DISK = 'D:\backup\testdb_full.bak' WITH INIT
2> GO

バックアップ(差分 : 前回フルバックアップ以降の更新分)

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> BACKUP DATABASE testdb TO DISK = 'D:\backup\testdb_diff.bak' WITH INIT,DIFFERENTIAL
2> GO

バックアップ(トランザクションログ : 前回ログバックアップ以降のログ)

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> BACKUP LOG testdb TO DISK = 'D:\backup\testdb_log_YYYYMMDDhhmmss.bak' WITH INIT
2> GO

バックアップ計画

• 月の第一営業日の業務終了後に「バックアップ(フル)」を実行。
• 月の第二営業日〜月末営業日の業務終了後に「バックアップ(差分)」を実行。
• 一日の営業時間中に適当な間隔で「バックアップ(トランザクションログ)」を実行。バックアップ先のファイルは毎回別のファイルに取る。ファイル名は昇順に並ぶように適当なシーケンス(YYYYMMDDhhmmssのような?)を付けると良い。

リストア(フル+差分+ログ)

障害が発生した時点で、アクティブなログ(=ログの末端)のバックアップを試みる

WITH NORECOVERYを指定してバックアップを取得した時点で、データベースがリストア(復旧)を受け付ける状態になり、通常の利用は出来なくなる。

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE master
2> BACKUP LOG testdb TO DISK = 'D:\backup\testdb_log_last.bak' WITH INIT,NORECOVERY
3> GO

上記ではバックアップできない場合は下記のいずれかの方法でバックアップを試みる(?)

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE master
2> BACKUP LOG testdb TO DISK = 'D:\backup\testdb_log_last.bak' WITH INIT,CONTINUE_AFTER_ERROR
3> GO

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE master
2> BACKUP LOG testdb TO DISK = 'D:\backup\testdb_log_last.bak' WITH INIT,NO_TRUNCATE
3> GO

その後、リストアを行う

以下の手順で、たとえばDROP DATABASE testdbされた様な状態からでも復旧可能。

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> USE master
2> RESTORE DATABASE testdb FROM DISK = 'D:\backup\testdb_full.bak' WITH NORECOVERY
3> RESTORE DATABASE testdb FROM DISK = 'D:\backup\testdb_diff.bak' WITH NORECOVERY
4> RESTORE LOG testdb FROM DISK = 'D:\backup\testdb_log_20090804080000.bak' WITH NORECOVERY
6> RESTORE LOG testdb FROM DISK = 'D:\backup\testdb_log_20090804083000.bak' WITH NORECOVERY
7> RESTORE LOG testdb FROM DISK = 'D:\backup\testdb_log_20090804090000.bak' WITH NORECOVERY
8> RESTORE LOG testdb FROM DISK = 'D:\backup\testdb_log_20090804093000.bak' WITH NORECOVERY
9> RESTORE LOG testdb FROM DISK = 'D:\backup\testdb_log_last.bak' WITH NORECOVERY
10> RESTORE DATABASE testdb WITH RECOVERY
11> GO

特定時刻の状態までリストアしたい場合

上記の例で、末端のログの2009年8月4日 9時45分時点の状態に戻すには以下のようにする。

9> RESTORE LOG testdb FROM DISK = 'D:\backup\testdb_log_last.bak' WITH NORECOVERY,STOPAT='2009/08/04 09:45:00'

ちょっとずつ確認しながら、特定時刻の状態までリストアしたい場合

9> RESTORE LOG testdb FROM DISK = 'D:\backup\testdb_log_last.bak' WITH STANDBY='standby_file.tmp',STOPAT='2009/08/04 09:31:00'
10> select * from testdb.dbo.確認対象のテーブル
11> GO
12> RESTORE LOG testdb FROM DISK = 'D:\backup\testdb_log_last.bak' WITH STANDBY='standby_file.tmp',STOPAT='2009/08/04 09:32:00'
13> select * from testdb.dbo.確認対象のテーブル
14> GO
15> RESTORE LOG testdb FROM DISK = 'D:\backup\testdb_log_last.bak' WITH STANDBY='standby_file.tmp',STOPAT='2009/08/04 09:33:00'
16> select * from testdb.dbo.確認対象のテーブル
17> GO
18> RESTORE DATABASE testdb WITH RECOVER
19> GO

ログからのリストアを、ちょっとずつ進めて、行きすぎても、時間を遡ってのリストアは出来ないので注意すること。その場合は再度最初からトライする...。

その他

実際業務で利用する場合は、コマンドをテキストファイルに打ち込んで -i オプションで実行するのが良いと思われる。打ち込んだテキストファイルは設定資料として、ファイル名に日時を入れて管理するとかどうだろう。

sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLExpress -s "," -W -i Hoge.sql