![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
SQL Server?
データベース作成
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ユーザ一覧
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> 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> 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.bak' 2> GO
バックアップ計画
" 月の第一営業日の業務終了後に「バックアップ(フル)」を実行。
" 月の第二営業日～月末営業日の業務終了後に「バックアップ(差分)」を実行。
" 一日の営業時間中に適当な間隔で「バックアップ(トランザクションログ)」を実行。
リストア(フル+差分+ログ)
障害が発生した時点で、アクティブなログ(=ログの末端)のバックアップを試みる
sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> BACKUP LOG testdb TO DISK = 'D:\backup\testdb_log.bak' WITH NORECOVERY 2> GO
上記ではバックアップできない場合は下記のいずれかの方法でバックアップを試みる
sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> BACKUP LOG testdb TO DISK = 'D:\backup\testdb_log.bak' WITH CONTINUE_AFTER_ERROR 2> GO
sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> BACKUP LOG testdb TO DISK = 'D:\backup\testdb_log.bak' WITH NO_TRUNCATE 2> GO
その後、リストアを行う
sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLEXPRESS
1> RESTORE DATABASE testdb FROM DISK = 'D:\backup\testdb_full.bak' WITH NORECOVERY 2> RESTORE DATABASE testdb FROM DISK = 'D:\backup\testdb_diff.bak' WITH NORECOVERY 3> RESTORE DATABASE testdb FROM DISK = 'D:\backup\testdb_log.bak' WITH RECOVERY 4> GO
その他
実際業務で利用する場合は、コマンドをテキストファイルに打ち込んで -i オプションで実行するのが良いと思われる。打ち込んだテキストファイルは設定資料として、ファイル名に日時を入れて管理するとかどうだろう。
sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLExpress -s "," -W -i Hoge.sql
テーブルをCSVファイルにエキスポートする方法
bcp testdb.dbo.testtable out Hoge.csv -U sa -P SA-PASSWORD -S localhost\SQLExpress -c -t "\t"" データベース : testdb
スキーマ : dbo(これが既定値の「スキーマ」。省略可能。) テーブル : testtable 出力先CSVファイル : out Hoge.csv ログインユーザ : -U sa パスワード : -P SA-PASSWORD 接続先ホスト : -S localhost 接続先インスタンス : /SQLExpress(既定のインスタンスに接続する場合は省略可能。) 出力形式 : -c (SJISテキストで出力する。UNICODE(UTF16LE)で出力する場合は -w) 区切り文字 : -t "\t" (タブを指定。)
テーブルをselectしてCSVファイルにエキスポートする方法
bcp "select * from testdb.dbo.testtable" queryout Hoge.csv -U sa -P SA-PASSWORD -S localhost\SQLExpress -c -t "\t"" 抽出クエリ : "select * from testdb.dbo.testtable"
出力先CSVファイル : queryout Hoge.csv ログインユーザ : -U sa パスワード : -P SA-PASSWORD 接続先ホスト : -S localhost 接続先インスタンス : /SQLExpress(既定のインスタンスに接続する場合は省略可能。) 出力形式 : -c (SJISテキストで出力する。UNICODE(UTF16LE)で出力する場合は -w) 区切り文字 : -t "\t" (タブを指定。)
CSVファイルをテーブルにインポートする方法
bcp testdb.dbo.testtable in Hoge.csv -U sa -P SA-PASSWORD -S localhost\SQLExpress -c -t "\t"" データベース : testdb
スキーマ : dbo(これが既定値の「スキーマ」。省略可能。) テーブル : testtable 入力CSVファイル : in Hoge.csv ログインユーザ : -U sa パスワード : -P SA-PASSWORD 接続先ホスト : -S localhost 接続先インスタンス : /SQLExpress(既定のインスタンスに接続する場合は省略可能。) 入力形式 : -c (入力CSVがSJISテキストファイルであることの指定。入力がUNICODE(UTF16LE)テキストファイルの場合は -w) 区切り文字 : -t "\t" (タブを指定。)
コメント：
