2024.04.18 IDENTITY 列の値をリセットする
テーブルに対して、DELETE と INSERT を繰り返していると、IDENTITY 列の値が虫食い状態となる。この現象を解消するには、ID 列を再作成する。
-- プライマリーキーの削除
ALTER TABLE TEST_TABLE DROP CONSTRAINT PK_TEST_TABLE;
--IDENTITY を設定した 列 Id の削除
ALTER TABLE TEST_TABLE DROP COLUMN [Id]
-- 列 Id を再作成
ALTER TABLE TEST_TABLE ADD [Id] [int] IDENTITY(1,1) NOT NULL
プライマリーキーの追加
ALTER TABLE TEST_TABLE ADD CONSTRAINT PK_TEST_TABLE PRIMARY KEY CLUSTERED (Id);
※リセット方法を調べると、DBCC コマンドに行きつく。しかし、このコマンドを実行しても IDENTITY 列を設定する開始番号を変更するだけで意味がない。INSERT 時に同じ値がすでに存在すると、エラーとなる。
SQL Server Management Studio (SSMS) がクラッシする
今日、突然 SQL Server Management Studio (SSMS) がクラッシュするようになった。これまで問題はなかったのだが、テーブルのデータを編集するメニューをクリックすると、空白のダイアログが表示されたのち、クラッシュするようになった。
思い当たる原因は明らかにある。先日 SQL Server を 2017 から 2019 に入れ替えたことだ。
こういう原因と症状の場合は、手っ取り早く SSMS を入れ替えた方が無難だ。原因追及といってもどこから手を付けるか考えるよりも早い。まずは SSMS をアンインストールして、念のために再起動したのだが、新規インストール時に再起動が必要と言われたので、もう一度再起動したのち、新規インストールをしたところ、あっさりとインストールできた。またクラッシュもなくなった。
SQL Server Management Studio のインストール
SQL Server Management Studio (SSMS) のダウンロード | Microsoft Docs
https://docs.microsoft.com/ja-jp/sql/ssms/download-sql-server-management-studio-ssms
インストールの際、日本語リンクをクリックして、日本語版をインストールする必要がある。
メジャー、マイナーバージョンアップ時、新しいバージョンをインストールすると、上書きインストールではなくて、複数同時インストールになることがある。
2024.04.10 テーブルごとのレコード件数・容量
SSMS を起動し、データベース右クリック - レポート - 標準レポート - テーブルごとのディスク容量で確認できる。
2024.03.02 SQL Server に SSL 接続する
前提条件
SQL Server がインストールされているサーバー (192.168.2.105) に対して、SSL 接続する。
設定関係
次のコマンドを実行して、ポート22 に着信したら、1433 にポートフォワーディングする。
netsh interface portproxy add v4tov4 listenport=22 listenaddr=192.168.2.105 connectport=1433 connectaddress=192.168.2.105
次のコマンドで、設定した結果を参照する。
netsh interface portproxy show v4tov4
次のコマンドで、設定を削除する。
netsh interface portproxy delete v4tov4 listenport=22 listenaddr=192.168.2.105
接続
SQL Server Managerment Studio を起動し、192.168.2.105,22 に接続する。
AWS (LightSail) に接続する場合
- listenaddr と connectaddress はサーバーのローカルアドレス。
- ポート 22 をファイアウォールで開けておく。
- 外部からの接続は、サーバーのグローバルアドレス, 22。
2024.02.23 Windows コマンドでデータを更新する。
Windows のコマンドプロンプトから sqlcmd を経由で、SQL を実行します。
sqlcmd -S localhost -d TEST_DB -Q "update MtbUser set ログインパスワード = 'test' where ログインid = 'admin'"
2024.02.23 データベースの圧縮バックアップができない。
SQL Server Express Edition は、圧縮バックアップをサポートしていないようだ。
sqlcmd -S localhost -Q "BACKUP DATABASE TEST_DB TO DISK='D:\TEST_DB.bak' WITH COMPRESSION"
BACKUP DATABASE WITH COMPRESSION は Express Edition (64-bit) ではサポートされません。
2024.02.23 SQL Server データベースのリストアができない
SQL Server データベースのリストアをコマンドから実行すると、「データベースは使用中なので、排他アクセスを獲得できませんでした。」のエラーが発生してリストアが途中で止まってしまうことがある。
SQL Server Management Studio から実行すれば、接続を閉じるにチェックを入れることで続行できるが、CUI はできない。
リストア用のコマンドを実行する前に、SQL Server を再起動するコマンドを実行する方法が手っ取り早い。
net stop "MSSQLSERVER"
net start "MSSQLSERVER"
sqlcmd -S localhost -Q "RESTORE DATABASE TEST_DB
FROM DISK='%~dp0TEST_DB.bak' WITH RECOVERY,
MOVE 'TEST_DB' TO 'C:\SQLDB\TEST_DB.mdf',
MOVE 'TEST_DB_log' TO 'C:\SQLDB\TEST_DB_log.ldf'"
2024.02.22 新しいバージョン SQL Server から古いバージョンの SQL Server にデータベース移行する
新しいバージョンの SQL Server で、データベース全体のスクリプトを作成します。その際、移行先の SQL Server のバージョンを指定します。
2023.07.15 SQL Server コマンドツールのみをセットアップする
SQL Server コマンドツールのみをセットアップするには、次のツールをインストールする。
1. Microsoft Visual C++ 再頒布可能パッケージ 17
サポートされている最新の Visual C++ 再頒布可能パッケージのダウンロード | Microsoft Learn
2. MIcrosoft ODBC Drvier 13 for SQL Server
Download Microsoft® ODBC Driver 13 for SQL Server® - Windows + Linux from Official Microsoft Download Center
3. Microsoft Command Line Utilities 13 for SQL Server
Download Microsoft® Command Line Utilities 13 for SQL Server® from Official Microsoft Download Center
2023.02.23 復元元フォルダに配置している bak ファイルを参照できない
SQL Server Management Studio からデータベースの復元を実行した場合、*.bak ファイルを配置しているフォルダを参照しても、*.bak ファイルを参照できず、復元ができない。
これはフォルダの権限の問題である。このフォルダまたはその上位のフォルダに対して、NT Service\MSSQLSERVER ユーザーの権限を追加することで参照できるようになる。このユーザーアカウントは、仮想アカウントであるため、通常の設定はできないかもしれない。
次のコマンドを入力して、権限を割り当てる。ユーザーごとの事実上のトップフォルダとなるユーザーフォルダに対して実行する必要があると思われる。
icacls "C:\Users\TestUser" /inheritance:r /grant "NT Service\MSSQLSERVER":(OI)(CI)F
2020.10.01 SQL Server : bak ファイルが復元できない
いつものように 他の SQL Server から bak ファイルをコピーして、自分の開発環境に復元しようとしたのだが、できない。よくよくエラー内容を見てみると、どうもバージョンが違うようだ。そういえば、他の SQL Server を 2019 をインストールしたのだった。
これでは不便なので、開発環境も 2019 に変更することにした。まずは 2017 を徹底的にアンインストールしてきれいにしてから 2019 をインストールした。こうしないと既定のインスタンス MSSQLSERVER が古い方にとられたままになってしまう。
2022.07.12 mdf, ldf のファイル名を変更する
SQL Server Management System では、ファイル名の変更はできないと思われる。データベースを復元する際にファイル名を指定しなおす。
通常の復元するコマンド
sqlcmd -S localhost -Q "RESTORE DATABASE SF_K1 FROM DISK='D:\SF_K1.bak' WITH RECOVERY, MOVE 'TestDB' TO 'C:\SQLDB\TestDB.mdf', MOVE 'SF_K1_log' TO 'C:\SQLDB\TestDB_log.ldf'"
ファイル名を変更して、復元するコマンド
sqlcmd -S localhost -Q "RESTORE DATABASE SF_K1 FROM DISK='D:\SF_K1.bak' WITH RECOVERY, MOVE 'TestDB' TO 'C:\SQLDB\TestDB_NEW.mdf', MOVE 'TestDB_log' TO 'C:\SQLDB\TestDB_NEW_log.ldf'"
復元後、SQL Server Management System でデータベース名と論理ファイル名を変更すればよい。
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - 信頼されていない機関によって証明書チェーンが発行されました。)
Data Source=(local);Initial Catalog=TestDB;Integrated Security=true;TrustServerCertificate=True
2022.06.27 Bool 型を保存するには bit 型が使用する
Bool 型 (True, False) を保存する場合には、テーブルの列を bit 型で定義する。True は 1、False は 0 で保存される。
2022.06.20 SSMS 変更の保存が許可されていません。
SSMSの初期設定では、テーブルのデザイン変更はできない。「変更の保存が許可されていません。」のエラーが発生する。
[ツール] - [オプション] - [デザイナー] - [テーブルデザイナーおよびデータベースデザイナー] を開き、「テーブルの再作成を必要とする変更を保存できないようにする」のチェックを外す。
2022.06.06 必要なファイルダウンロードできませんでした
SQL Server をサイレントインストールしようと考え、以前成功した方法で行ったところ、次のエラーが発生しました。
---------------------------
次のエラーが発生したため、SQL Server インストーラー を続行できません。
必要なファイルをダウンロードできませんでした。インストーラーのバージョンがサポートされなくなっている可能性があります。ダウンロード サイトからもう一度ダウンロードしてください。
---------------------------
保存しておいたSQL2019-SSEI-Expr.exe が更新されて古くなっていたことが原因のようです。あたらにダウンロードして実行したところ、成功しました。
2022.06.04 SQL Server 2019 の環境に SQL Server 2017 をインストール
SQL Server 2019 の環境に SQL Server 2017 をインストールを試してみた。
1. SQL Server 2019 をインストール。インスタンス名は MSSQLSERVER とした。
2. SSMS をインストールし、MSSQLSERVER に接続し、バージョンを確認 (select @@version) すると、SQL Server 2019 が表示された。
3. SQL Server 2017 をインストール。インスタンス名は MSSQLSERVER とした。同じインスタンスが存在しているため、エラーとなりインストールに失敗した。
4. SQL Server 2017 をインストール。インスタンス名は SQLEXPRESS とした。
5. SSMS から SQLEXPRESS に接続し、バージョンを確認 (select @@version) すると、SQL Server 2017 が表示された。
2022.04.26 コマンドプロンプトから SQL を実行する
Windows のコマンドプロンプトから SQL を実行するには sqlcmd を使用します。 データベース DB_A の テーブル TBL_A の COL_A 列をアップデートするには、以下のコマンドを実行します。 sqlcmd -S localhost -d DB_A -Q "update TBL_A set COL_A = 'TEST' where COL_B = '1'"
2020.04.22 SQL Server 構成マネージャが見つからない
通常はスタートメニューにあるのですが、発見できないことがあります。 
この場合は、SQLServerManager*.msc でファイルを検索します。C:\Windows\System32 に存在します。次の通りファイル名のバージョンが違います。
- SQL Server 2014 : SQLServerManager12.msc
- SQL Server 2017 : SQLServerManager14.msc
- SQL Server 2019 : SQLServerManager15.msc
2020.04.21 マルチユーザーとシングルユーザーモードの切り替え
マルチユーザーとシングルユーザーモードの切り替えは以下の方法で行います。
- SQL Server Management Studio で SQL Server に接続します。
- データベースのプロパティを開きます。
- オプションタブを開きます。
- アクセスの制限で、MULTI_USER / SINGLE_USER を切り替えます。
- プロパティを閉じます。
2022.04.20 SQL Server 起動不能
SQL Server 2017 で次のエラーが発生して起動不能となりました。 An error occurred while processing the log for database. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log. ログを再構築するにも SQL Server に接続できないと意味がありません。また仮に master データベースをバックアップしていても restore コマンドは実行できないのではないか。 調べてみると、セットアッププログラムからシステムデータベース全般の再構築ができることが判明しました。l Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=sa /SAPWD=P@ssword ところがこれを実行すると、次のエラーが発生しました。不思議... Windows アカウント sa は存在しないため、SQL Server のシステム管理者として準備できません。 とりあえず、sa ユーザーを管理者として作成してから、もう一度 Setup を実行すると無事復旧しました。 システムデータベースの内容はクリアされるので、実際のデータが入っているデータベースは *.bak ファイルから復元しました。
2022.04.19 データベースのバックアップの復元
SQL Server に対して、Restore コマンドを使って復元する際、データベースにロックがかかって復元できないことが度々発生します。 SQL Server Management Studio から復元する際には、ロックを解除するチェックがありますが、コマンドからだとその機能がないようです。 そこで、思い切って、SQL Server を一度再起動して復元すると確実にうまくいきます。そのためのコマンドは次の通りです。 net stop "MSSQLSERVER" net start "MSSQLSERVER" sqlcmd -S TestServer -Q "RESTORE DATABASE TestDB FROM DISK='%~dp0TestDB.bak' WITH RECOVERY, MOVE 'TestDB' TO 'C:\SQLDB\TestDB.mdf', MOVE 'TestDB_log' TO 'C:\SQLDB\TestDB_log.ldf'" ~dp0 はカレントディレクトリを指しますが、復元元の *.bak ファイルを保存しているディレクトリに対して、NT Service\MSSQLSERVER ユーザーの権限を追加しておく必要があります。これらのコマンドをバッチファイルとして作成しておき、管理者として実行すれば、数秒でデータベースの復元ができます。SQL Server Management Studio を使用すると、何度もクリックする必要があるので、テスト環境では効率的です。
コマンドで追加する場合は、次の通り、親フォルダを継承した上で、追加しています。
icacls "C:\SQLServer" /inheritance:e /grant mssqlserver:(OI)(CI)F
SSMS (SQL Server Management Studio) でT-SQL のデバッグができない
SSMS 18.0 から同機能が削除されたようです。非常に痛いです。 https://docs.microsoft.com/ja-jp/sql/ssms/release-notes-ssms?view=sql-server-ver15#180 代わりに Visual Studio を使用することができます。
SQL Server のサイレントインストール
SQL Server をサイレントインストールする方法です。 SQL2019-SSEI-Expr.exe でもインストールは可能ですが、どうしてもインスタンス名を変更できないため、 SQL2019-SSEI-Expr.exe で実ファイルをダウンロードしたうえで、セットアップを実行します。ついでにサーバーに対して、TCP/IP 接続を許可し、ファイアウォールも許可します。
mkdir C:\Downloads SQL2019-SSEI-Expr.exe /ACTION=Download MEDIAPATH=C:\Downloads /MEDIATYPE=Core /QUIET cd C:\Downloads SQLEXPR_x64_ENU.exe /QS /IACCEPTSQLSERVERLICENSETERMS /ACTION="install" /INSTANCENAME=MSSQLSERVER /SECURITYMODE=SQL /SAPWD=P@ssword /TCPENABLED=1 netsh advfirewall firewall add rule name="MSSQL" protocol=TCP dir=in localport=1433 action=allow
SQL Server Management Studio (SSMS) でデータースなどの名前変更の際 Del が反応しない
SSMS の左側のツリービューで、データベースを選択し、F2 で名前変更を行う際に、Del キーが反応しません。BackSpace キーを使うしかありません。
SQL Server のセットアップを解凍・展開する
SQL Server 2017 with Advanced Service のセットアップファイルを解凍・展開する。F:\Test フォルダは自動的に作成される。
SQLEXPRADV_x64_JPN.exe /x:F:\Test /q
LocalDB のインストール
msiexec /i SqlLocalDB.msi /qn IACCEPTSQLLOCALDBLICENSETERMS=YES