2

I'm ready to pull my hair out. I made a script to backup and restore DB and Filegroup that build query like :

RESTORE DATABASE [dashdb_dev01_2024-04-11 143711] FROM DISK = 'E:\SQL2019_Data\Backup\dev01\20240410\1437\dashdb_dev01_2024-04-11 143711.bak' WITH REPLACE, RECOVERY, MOVE [dashdb_dev01_2024-04-11 143711] TO 'E:\SQL2019_Data\Backup\OLD\dashdb_dev01_2024-04-11 143711.mdf'

And I got :

Msg 102, Level 15, State 1, Server FRPARPLMA-DBDEV, Line 1
Incorrect syntax near 'dashdb_dev01_2024-04-11 143711'.

I use [...] to escape db name, but I understand from the MSG 102 that's the 102th char (I'm right here ?). So it's the 'E:\SQL2019_Data\Backup\dev01\20240410\1437\dashdb_dev01_2024-04-11 143711.bak' after the FROM DISK.

How can I escape, I think, the space here?

1 Answer 1

6

If you format your command over multiple lines then you will get a more specific error message.

Restore Command

RESTORE DATABASE [dashdb_dev01_2024-04-11 143711] 
    FROM DISK = 'E:\SQL2019_Data\Backup\dev01\20240410\1437\dashdb_dev01_2024-04-11 143711.bak' 
    WITH REPLACE, 
        RECOVERY, 
        MOVE [dashdb_dev01_2024-04-11 143711] 
        TO 'E:\SQL2019_Data\Backup\OLD\dashdb_dev01_2024-04-11 143711.mdf'

Error Message

Msg 102, Level 15, State 1, Line 5 <------ HERE THE MORE SPECIFIC LINE NUMBER
Incorrect syntax near 'dashdb_dev01_2024-04-11 143711'.

Completion time: 2024-04-18T11:26:04.3459668+02:00

SQL Server Management Studio

If you look at the statement in SSMS then you will notice a red underlined part:

SQL Server Management Studio - Script underlined error

This is where the error is occuring.

Solution

The syntax for the RESTORE ... command is well documented at RESTORE Statements (Transact-SQL) and the solution is to use single quotes ' for the logical name according to this section of the documentation:

<general_WITH_options> [ ,...n ]::=
--Restore Operation Options
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
          [ ,...n ]

So your script would look like this:

RESTORE DATABASE [dashdb_dev01_2024-04-11 143711] 
    FROM DISK = 'E:\SQL2019_Data\Backup\dev01\20240410\1437\dashdb_dev01_2024-04-11 143711.bak' 
    WITH REPLACE, 
        RECOVERY, 
        MOVE 'dashdb_dev01_2024-04-11 143711' TO 'E:\SQL2019_Data\Backup\OLD\dashdb_dev01_2024-04-11 143711.mdf';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.