Syntax error when calling a stored procedure with nested strings in the parameters

Question

I want to restore database in SQL Server 2008 R2 using a bak file and stored procedure of Lite speed. Below is my Code:

USE Master
Go 
exec master.dbo.XP_RESTORE_DATABASE
@database="abc"
,@filename='M:\BACKUPS\xyz.bak'
,@WITH= 'REPLACE'
,@WITH= 'MOVE' "abc_DATA" TO "H:\SQLDATA\ABC.mdf"'
,@WITH= 'MOVE' "abc_LOG" TO "H:\SQLDATA\ABC.ldf"'
GO

After executing the above query I got below error:

Incorrect Syntax near 'abc_DATA'.

I don't know why I am getting this error as I always used this query to restore database.

That doesn't look right - you have imbalanced ' characters there. A quick hunt suggests that the ' immediately after MOVE shouldn't be there. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 10, 2017 at 7:21
xp_restore_database is not a SQL Server procedure. It's a part of the Litespeed product — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 10, 2017 at 7:37
Litespeed's docs for xp_restore_database. In any case, your string parameter terminates righ after 'MOVE'. That second ' means only MOVE is part of the string. Remove it in both cases, ie write ,@WITH= 'MOVE "abc_DATA" TO "H:\SQLDATA\ABC.mdf"' — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 10, 2017 at 7:38
@PanagiotisKanavos - the litespeed tag doesn't appear to be anything to do with a database product. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 10, 2017 at 7:42

Stephen Whitfield · Accepted Answer · 2017-10-10 07:34:48Z

1

This should work:

RESTORE DATABASE [abc]
FROM DISK = [DiskLocation]
WITH MOVE 'abc_data' TO ' H:\SQLDATA\ABC.mdf'
,MOVE 'abc_log' TO 'H:\SQLDATA\ABC.ldf'

answered Oct 10, 2017 at 7:34

Stephen Whitfield

816 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Damien_The_Unbeliever Over a year ago

It appears that they're using a separate product for managing their backups. Possibly with compression. It's unlikely that the native RESTORE command can comprehend the contents of the backup file.

Stephen Whitfield Over a year ago

Thanks for pointing that out Damien, i wasn't initially aware.

Panagiotis Kanavos · Accepted Answer · 2017-10-10 07:57:52Z

0

XP_RESTORE_DATABASE isn't a SQL Server procedure. It's part of Quest's LiteSpeed product . The docs for xp_restore_database are here

Second, the message explains what's wrong. The call isn't correct. The string value passed to the @WITH parameter is terminated right after MOVE and followed by an object identifier "abc_DATA".

The call should be :

exec master.dbo.XP_RESTORE_DATABASE @database="abc", @filename='M:\BACKUPS\xyz.bak'
        ,@WITH= 'REPLACE'
        ,@WITH= 'MOVE "abc_DATA" TO "H:\SQLDATA\ABC.mdf"'
        ,@WITH= 'MOVE "abc_LOG" TO "H:\SQLDATA\ABC.ldf"'

You'll notice that syntax coloring paints the entire string red now.

answered Oct 10, 2017 at 7:57

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Collectives™ on Stack Overflow

Syntax error when calling a stored procedure with nested strings in the parameters

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related