Microsoft SQL Server

Mirosoft SQL Server Tips

2024.04.16 Microsoft SQL Server Management Studio がビジー状態です

SSMS が頻繁にビジー状態になる。

IntelliSense を無効にすると、良くなるらしい。とりあえず、これで様子を見る。

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 マルチユーザーとシングルユーザーモードの切り替え

マルチユーザーとシングルユーザーモードの切り替えは以下の方法で行います。

  1. SQL Server Management Studio で SQL Server に接続します。
  2. データベースのプロパティを開きます。
  3. オプションタブを開きます。
  4. アクセスの制限で、MULTI_USER / SINGLE_USER を切り替えます。
  5. プロパティを閉じます。

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

-Microsoft SQL Server