Microsoft SQL Server 2020 Expressの定期バックアップができない

Question

実現したいこと

Microsoft SQLServerのバックアップについて、1日1回自動で行うように作成したいです。
SQLServer Expressの場合、自動バックアップを行う機能を利用できないため手動でシステムを作る必要があるのですが、バックアップファイルの処理がうまく起動しません。

発生している問題・分からないこと

https://sigmanest.jp/blog-sqlserverbackup/

上記のサイトを参考に
1 - SQLファイルの作成、
2 - バッチファイルの作成
ここまでを行いバッチファイルを起動してみたのですがバックアップデータが保存されていませんでした。

スクリプトを作成した際その内容を確認したのですが、一部の箇所が赤文字表記になっていました。
原因の可能性があると思うのですが、どのように変更すればいいかわかりません。
ご教授のほどお願い致します。

該当のソースコード

BACKUP DATABASE [DB名] TO  DISK = 
N'C:バックアップ先' (この箇所が赤文字表記)
WITH NOFORMAT, NOINIT,  NAME = 
N'DB名-完全 データベース バックアップ'(この箇所が赤文字表記), 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

https://sigmanest.jp/blog-sqlserverbackup/

作成の流れは上記のサイトと同じです。バッチファイルもサイトの作成通りに行いました。

☆
sqlcmd -S サーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード
-i 実行するSQLファイルのフルパス>バックアップファイルのフルパス
・powershellにて実行後次のエラーが表示されました。

メッセージ 102、レベル 15、状態 1、サーバー サーバー名\SQLEXPRESS、行 1
'S' 付近に不適切な構文があります。
メッセージ 132、レベル 15、状態 1、サーバー サーバー名\SQLEXPRESS、行 1
ラベル 'C' は、既に宣言されています。ラベル名は、クエリ バッチまたはストアド プロシージャ内で一意にしてください。
☆

☆
バッチファイルの内容

sqlcmd -S サーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード -i 実行するSQLファイルのフルパス>バックアップファイルのフルパス

・パッチファイルは起動自体はできます。
・メモ帳による作成(UTF-8→ANSI(シフトJIS)に変換後実行)
・バックアップファイルのフルパスを変えてみたのですが保存されていませんでした。
☆

・サーバー名はMicrosoft SQL Server を立ち上げた際に表記される「サーバー名」
SQLServerのユーザー名は認証の下に入力する「ログイン」
SQLServerのパスワードはログインの下にある「パスワード」と同じです。
実行するSQLファイルのフルパスは1.で作成したスクリプトファイルのパスです。
バックアップファイルのフルパスはスクリプトしたコードのバックアップ先と同じです。

・スクリプト化したファイルを開くとSQL Server　management　studioが開きログイン画面が表示されます。

・Start-Process " C:\ファイル名.bat"

ターミナルでも実行自体はできます。
しかし、実行の際一瞬処理のためにか真っ黒な画面が表示されるだけで、エラーによって処理を止められたわけではなく、実行処理を終えるだけで保存はできないです。

補足

・SQL Server　management　studio 2020
・windows11 home、64ビット

Comments

[yametai]

> 一部の箇所が赤文字表記になっていました。

SQL Server Management Studioは正常に実行できるけど赤文字表記になることがあったような気がします。
直接的な原因じゃないと思いましたので、まず下に記載した内容を確認してみると良いかなと思いました。

該当のソースコードのスクリプトをSQL Server Management Studioから実行してみたらどうですか？
バッチファイルをダブルクリックで実行するのではなく、ターミナルなどから実行したらどうですか？
何かエラーが出ましたらそのエラー内容を質問欄にご記載いただくと良いかなと思いました。

[syuu07]

コメントありがとうございます。

スクリプトをSQL Server Management Studioから実行
→SQL Server Management Studioを事前にログインしているかどうかで処理が変化します。
・事前にログインして開いている・・・コードが出力され実行すればバックアップファイルが保存されます。
・事前にログインせず開いていない・・ログイン画面が表示されます。ログインするとコードが出力されますがもう一度ログイン画面が開いてきます。2度ログインすると実行できるようになりバックアップファイルが保存されます。
いずれにせよファイル自体の保存は可能です。

ターミナルなどから実行
→ターミナルから直接ログインし、ログイン後にバックアップの処理を行うということでしょうか？
一度powershellから実行させましたが追記させましたエラーが発生しました。

[yametai]

コメントありがとうございます。

> いずれにせよファイル自体の保存は可能です。

バックアップのスクリプト自体は問題なさそうですね。

> →ターミナルから直接ログインし、ログイン後にバックアップの処理を行うということでしょうか？

意図を書いていなくてごめんなさい。
バッチファイルをダブルクリックで実行してしまうとすぐに閉じてしまうと思います。
エラーが表示されていてもそれを確認することができないと思います。
そのためターミナル(PowerShell？)を開いて下のような感じで実行します。
そうするとターミナルは開いたままですのでエラーメッセージなどが確認できます。
(バッチファイル名は作成したものに合わせて変更してください)
(PowerShellとコマンドプロンプトで記述が異なるかもしれませんので調べてみてください)
`.¥test.bat`

エラーメッセージなどがあれば質問欄にご記載ください、
(ログイン自体はバッチファイル内のsqlcmdの引数に記述されていますのでバッチを実行するだけでログインも自動的にされると思いますよ)

---

途中まで書いていたら質問欄が編集されたようでした。
PowerShellから実行してみたのですね。
これはsqlcmdを実行していますでしょうか？
sqlcmdではなくバッチファイルを実行してみてください。

[dodox86]

> sqlcmd -Sサーバー名-U SQLServerのユーザー名-P SQLServerのパスワード

sqlcmdコマンドの指定で、"-S"や"-U"、"-P"オプション等、前後のスペースが正しく入っていないように見えます。質問文中に書くときに間違えたのかバッチファイルにもともと間違えて記載してしまっているのか分かりませんが、正確に記載して試してください。

[syuu07]

dodox86様

申し訳ございません。
貼り付け後に自動で空白が削除されていました。変更いたしました。

[syuu07]

yametai様

ターミナルから　`.¥ファイル名.bat`　を行ったところエラーが発生しました。

.¥ファイル名.bat` : 用語 '.¥ファイル名.bat`' は、コマンドレット、関数、スクリプト ファイル、または操作可能なプログラムの名前として認識されません。名前が正しく記述されていることを確認し、パスが含まれている場合はそのパスが正しいことを確認してから、再試行してください。
発生場所 行:1 文字:1
+ `.¥ファイル名.bat`
+ ~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (.¥ファイル名.bat`:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

内容的にはパスが間違っているそうなので原因を探します。

また、関係あるかはわかりませんがスクリプト化したSQL文を直接入力することでバックアップファイルを保存することはできました。

sqlcmd -Sサーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード

BACKUP DATABASE [DB名] TO DISK =
N'C:バックアップ先' WITH NOFORMAT, NOINIT, NAME =
N'DB名-完全 データベース バックアップ', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

[yametai]

ごめんなさい・・
「`」(バッククォート)はteratailの回答欄などに入力する場合のインラインコードを表す記号です。
もし「`」を入力しているようでしたら実際に実行する際はこの記号は不要ですね。
.¥ファイル名.bat

[dodox86]

@質問者 syuu07さん

[2024/07/24 09:43]に修正された文を確認しましたが、

> sqlcmd -Sサーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード -i 実行するSQLファイルのフルパス>バックアップファイルのフルパス

"-Sサーバー名"の部分で空白が入っていません。「正確に記載しましょう」と書いたのは、質問文もそうですがそれよりはむしろ実際のバッチファイルの実行や手入力での実行で、正しくオプションを指定できていないのでエラーになっているのでは？と言う危惧からです。恐らく本質問を読んだ私以外の方も、そういう心配をされると思います。

[syuu07]

dodox86様

申し訳ございません。
こちらの確認不足です。以後、気を付けます。

[nonoshu]

バックアップ先にCドライブ直下は出来なかったような・・・

とりあえず質問文をもう少し正確に、
情報がわかるとまずい部分は面倒でも伏せ字に置き換えるなど
正確なコードを提示した方がより正確な回答も集まると思います。

[syuu07]

nonoshu様

バックアップ先にCドライブ直下
→SQL Server　management　studio からバックアップの処理を行ったところ、Cドライブからでも可能でした。

コードについては伏字以外はすべてコピーペーストし一度修正しました。

[syuu07]

yametai様

Start-Process " C:\ファイル名.bat"

ターミナルで実行ができました。
しかし、実行の際一瞬処理のためにか真っ黒な画面が表示されるだけで、エラーによって処理を止められたわけではなく、実行するだけで保存はできていない状況です。

[yametai]

呼び出し演算子を使用する
[コマンドの優先順位について - PowerShell | Microsoft Learn](https://learn.microsoft.com/ja-jp/powershell/module/microsoft.powershell.core/about/about_command_precedence?view=powershell-7.4#use-the-call-operator)

ファイル名などにスペースなどが含まれているのでしょうか？
(あるいはバッチファイルのある場所をカレントフォルダとして実行していないのでしょうか？)
Start-Processを使ってしまうとエクスプローラーからダブルクリックしたのと同じ感じになってしまいますので、
「&」(呼び出し演算子)と「'」(シングルクォーテーション)を使って次のように呼び出すと良いかなと思います。

& 'C:\ファイル名.bat'

[nonoshu]

画像を貼り付けたかったので回答に記述しました。
実行環境をもう少し詳しく記載出来ますか？
（OS等）
batファイルを実行しているのはSQL-Serverがインストールされた端末ですか？
バックアップファイルの保存先はSQL-Serverがインストールされた端末のディスク内である必要があります。

[syuu07]

nonoshu様

実行環境
→追記しました。

batファイルを実行しているのはSQL-Serverがインストールされた端末ですか？
→はい。1つのPCでSQL-ServerをダウンロードしそのPCでbatファイルを実行しています。

[syuu07]

yametai様

ファイル名などにスペースなどが含まれているのでしょうか？
→スペースをいれないで実行していました。
念のため、一度作り直しましたが特に変わらなかったです。

& C:\ファイル名.bat
→別のエラーが表示されました。

アンパサンド (&) 文字は許可されていません。& 演算子は、今後使用するために予約されています。アンパサンドを二重引用符で囲
み ("&")、文字列の一部として渡してください。
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : AmpersandNotAllowed

"&" C:\ファイル名.bat

発生場所 行:1 文字:5
+ "&" C:\ファイル名.bat
+ ~~~~~~~~~~~~~~
式またはステートメントのトークン 'C:\ファイル名.bat' を使用できません。
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : UnexpectedToken

[dodox86]

> .¥ファイル名.bat` : 用語 '.¥ファイル名.bat`' は、コマンドレット、関数、スクリプト ファイル、または操作可能なプログラムの名前として認識されません。名前が正しく記述されていることを確認し、パスが含まれている場合はそのパスが正しいことを確認してから、再試行してください。
> 発生場所 行:1 文字:1
> + `.¥ファイル名.bat`

"ファイル名.bat"の直後にバッククォーテーション(`)が入ってますよ。ひとつひとつ確実に入力して確認しましょう。これではどこでNGなのか本質問を読んでいる方々に分からないと思います。

[syuu07]

dodox86様

コメントありがとうございます。
修正させていただきました。ご迷惑をおかけして申し訳ございません。

[yametai]

色々書き込みしてしまってごめんなさい・・
回答に必要なエラーメッセージを表示するまでに長いやり取りになってしまっていますね・・

SQL Server Management Studioからsqlcmdを実行して正しくバックアップできているみたいですので、sqlcmdの内容は問題ないと思います。
多分バッチの内容が正しくないような印象です。
そのバッチで何が起きているのか止めてエラーメッセージを確認したいのですけどね。

バッチファイルはメモ帳で作成していますか？
バッチファイルの文字コードはshift_jisである必要があるのですが、そのあたりの認識は大丈夫でしょうか？
Windows11のメモ帳はデフォルトでUTF-8の文字コードだったような？
バッチファイルの作成する際のエディタや文字コードの情報も質問欄にご記載いただくと良いかもしれません。
＊根本的なコマンドの問題がある場合はバッチファイルの中でpauseしても止められず一瞬でウィンドウが消えてしまったような気がします。

テスト用に「C:\folder1\test.bat」を作成して、「C:\folder1」フォルダの中でターミナルを開いて次の2つのコマンドを入力してみていただけますでしょうか。
＊「test.bat」は正しく実行できたsqlcmdの内容を記述してみてください。文字コードはshift_jisです。

ls
.\test.bat

[syuu07]

yametai様

バッチファイルはメモ帳で作成していました。
そのため、UTF-8の文字コードでの実行になっていました。ANSI(シフトJIS)に変換しtest.batとして保存しました。
実行結果は以下の通りです。

PS C:\folder1> ls

ディレクトリ: C:\folder1

Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 2024/07/25 9:08 183 test.bat

PS C:\folder1> .\test.bat

C:\folder1>sqlcmd -S サーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード -i 実行するSQLファイルのフルパス Files\Microsoft SQL Server\バックアップファイルのフルパス>C:\Program
アクセスが拒否されました。
PS C:\folder1>

[yametai]

ご確認ありがとうございます。

> アクセスが拒否されました。

アクセスが拒否されていますね・・

> 2024/07/24 10:07 編集
> また、関係あるかはわかりませんがスクリプト化したSQL文を直接入力することでバックアップファイルを保存することはできました。

上のコメントではバックアップできていたみたいですが、
sqlcmdの対話形式でBACKUPコマンドを入力したということですかね。

バッチファイル内に記述している「実行するSQLファイル」、「バックアップファイル」のフルパスに「Program Files」とか「Microsoft SQL Server」とかスペースがあって問題になっているのでしょうかね？
その場合はパスを「"」(ダブルクォーテーション)で囲んでみるとか？
SQLを直接入力した際も「Program Files」の下を使用していましたでしょうか？
権限がないかもしれませんので別の場所を使用するようにしてみるとか？

[syuu07]

yametai様

バッチファイル内に記述している「実行するSQLファイル」、「バックアップファイル」のフルパスに「Program Files」とか「Microsoft SQL Server」とかスペースがあって問題になっているのでしょうかね？
→これが原因でした。
空白の無いパスで試したところ、無事にバックアップファイルが取得できました。

ここまでご回答、本当にありがとうございました。

[yametai]

コメントありがとうございます。
解決したのですかね？でもバッチからバックアップが取得できたようで良かったです。
自己解決でクローズするか、すでについている回答が参考になったらそちらで解決・クローズにするかしておくと良いかなと思います。

[syuu07]

yametai様

コメントありがとうございます。
既についている回答をベストアンサーとし、回答を終わらせたいと思います。

改めまして、ここまでご回答ありがとうございました。

Answer 1

batファイルの最終行に pause を記述すると
黒い画面が閉じずに残り続けます。

例

sqlcmd -S サーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード
pause

こんな感じに何かエラーメッセージが表示されませんか？

Comments

[syuu07]

ご回答ありがとうございます。

実行させましたが特にエラーは起きず処理を終えました。

C:> sqlcmd -S サーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード
1> pause
2>

[dodox86]

@質問者 syuu07さん

本回答をくださったnonoshuさんの意図としては、バッチファイルを実行した時、もとのコードだと正常終了もエラー終了でもコンソールが閉じてしまうので、最後にpause を入れることでエラー時に恐らくその旨の出力を確認できるでしょう、と言うことだと思います。

syuu07さんが試した以下の手順は、バッチファイルの実行ではなく、コンソール上からの手入力ですよね。ユーザー名とパスワード自体は正しいのだろうから、sqlcmdでSQL Serverにログオンしてしまっています。だから、プロンプトが"1>" "2>"になってしまっています。これは、コマンドプロンプトでもPowerShellでもなく、sqlcmdの実行中のプロンプトです。

C:> sqlcmd -S サーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード
1> pause
2>

(私を含めて)色々な方が回答やコメントをすると混乱すると思いましたのでコメントは差し控えてました。これで最後にしようと思います。

[syuu07]

dodox86様

ここまで丁寧なご回答、ありがとうございました。

test.bat

sqlcmd -S サーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード
pause

上記のコードをバッチファイルにて保存しバッチファイルから実行させました。
結果としては特にエラーメッセージは表示されなかったです。

C:\Users\sample>sqlcmd -S サーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード
1>

[nonoshu]

dodox86さん
ご解説ありがとうございます。ご解説の通りです。

質問者さん
私の回答内で記述したtest.batはあくまで「例」ですから
質問者さんの実際に行いたいbatファイルの最終行に
pause　を記述して実行して
エラーが出力されていないか確認してみてください。

[syuu07]

nonoshu様

ご回答ありがとうございます。
意図を理解できず、申し訳ございませんでした。

実際に行いたいパッチファイルは以下の通りでしたので、このパッチファイルにpauseをつけました。

sqlcmd -S サーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード -i 実行するSQLファイルのフルパス>バックアップファイルのフルパス
pause

変更後バッチファイルを実行した結果、エラーが表示されました。

C:>sqlcmd -S サーバー名 -U SQLServerのユーザー名 -P SQLServerのパスワード -i 実行するSQLファイルのフルパス Files\バックアップファイルのフルパス 1>C:\Program
アクセスが拒否されました。

C:>pause
続行するには何かキーを押してください . . .