CODEWITHSUNDEEP
sql-serverpowershell
StuiterSlurf
StuiterSlurf

Reputation: 2572

Restore SQL database - Incorrect syntax near '-'

I'm trying to restore a database with a - in the name. If I have no - in the name, the database is correctly restored.

If I put '' around the database it still doesn't work. I'm still searching what to do but I can't find it. 

$sqlRestoreCommand.CommandText = "RESTORE DATABASE '$databaseName' FROM DISK = '$databaseBackup' " ;

The following exception occurs and the code is below:

Exception: Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '-'. 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.".Exception.Message

$databaseName = "TempRpc-RC";

# Write the state for debugging purposes.
"SQL State" > sqlstatelog.txt;
$connection.State >> sqlstatelog.txt;

# Create the SQL restore command.
$sqlRestoreCommand = $connection.CreateCommand();

# Set the command text to a SQL restore command and fill in the parameters.
# With move option is needed!
$sqlRestoreCommand.CommandText = "RESTORE DATABASE $databaseName FROM DISK = '$databaseBackup' " ;
$sqlRestoreCommand.CommandText += "WITH FILE = 1, ";
$sqlRestoreCommand.CommandText += "MOVE N'$databaseName" + "'" + " TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Data\$databaseName" + "_Data.mdf', ";
$sqlRestoreCommand.CommandText += "MOVE N'$databaseName" + "_log'" +" TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Data\$databaseName" +"_Log.ldf'";
$sqlRestoreCommand.Connection = $connection;

# Execute the commands.
$sqlRestoreResult = $sqlRestoreCommand.ExecuteNonQuery();

# Write the query
Write-Verbose $sqlRestoreCommand.CommandText;

Upvotes: 1

Views: 3305

Answers (1)

mklement0
mklement0

Reputation: 438133

tl;dr:

Inside the expandable PowerShell string ("...") that defines the query string, replace '$databaseName' with [$databaseName]

As an aside: Unless you fully control all parts used to construct the query string, you should use parameters to avoid SQL injection attacks, as Ansgar advises.

Ansgar Wiechers provided the crucial pointer:

Since your database name contains a - character, it isn't a regular identifier, so it must be quoted / delimited, i.e., it must be specified as a delimited identifier.
Additionally, identifiers that conflict with reserved keywords must be specified as delimited identifiers too.

A delimited identifier is one enclosed in either [...] or "..."; by contrast, '...' is not supported, which explains your symptoms.

Note: [...] is SQL Server-specific, but is always supported there, whereas the standard "..." is only supported when SET QUOTED_IDENTIFIER ON is in effect in T-SQL, which is true by default, however - except in certain situations, such as when using sqlcmd.exe without -l.

Upvotes: 2

Related Questions