Search code examples
batch-filesqlcmd

Restoring .bak file from batch file

I am automating a test environment setup process and below are part of the manual steps involved:

  1. Copy a .bak file from a network drive to my D: drive
  2. Launch SQL Management Studio, use the Restore Files and Filegroups option to restore the .bak file

For point #1, I used:

robocopy \\10.xx.xxx.x\dirA\Build\%myVar%\DB\ D:\ myDB_V%myVar%.bak

It copied just fine!

Now for point 2, I am trying the below command using sqlcmd from command prompt:

sqlcmd -E -S localhost\myInstance -Q "RESTORE DATABASE myDB_V2.2.2.10 FROM DISK='D:\myDB_V2.2.2.10.bak' WITH FILE = 1, MOVE 'myDB_V2.2.2.10' TO 'C:\Program Files\Microsoft SQLServer\MSSQL10.SQLEXPRESS\MSSQL\DATA\myDB_V2.2.2.10.mdf', MOVE 'myDB_V2.2.2.10_log' TO 'C:\Program Files\Microsoft SQLServer\MSSQL10.SQLEXPRESS\MSSQL\DATA\myDB_V2.2.2.10.mdf';"

It says

Msg 102, Level 15, State 1, Server localhost\myInstance, Line 1
Incorrect syntax near '.2'.
Msg 319, Level 15, State 1, Server localhost\myInstance, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I could not interpret what it is saying.

Can anyone help me please?

Solution

  • It looks like the error is about your database name, "RESTORE DATABASE myDB_V2**.2**.2.10 [...]". I would suggest not using periods in your database name as periods are used to separate database objects in queries. If you are already using periods, try wrapping the database name in square brackets [].