Restore script syntax error - SQL Server 2008R2

In doing a restore  from SQL Server 2000 to 2008R2, I received the following error message:

System.Data.SqlClient.SqlError: RESTORE detected an error on page (29285:1953383791) in database "MBACRM-SQL-2008R2" as read from the backup set. (Microsoft.SqlServer.Smo)

In researching here, I found this article:

http://www.experts-exchange.com/Database/Miscellaneous/Q_23966944.html

I am now trying to restore without full-text using a SQL Server script:

RESTORE DataBase MBACRMSQL2008R2
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAXIMIZER\MSSQL\Backup\MBACRM-SQL2000.bak'
with MOVE 'MBACRM' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAXIMIZER\MSSQL\DATA\MBACRMSQL2008R2.mdf';
with MOVE 'MBACRM_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAXIMIZER\MSSQL\DATA\MBACRMSQL2008R2_log.ldf'
GO

I was able to get rid of other errors when parsing it, but I still get this one ... and cannot figure out what is causing it:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'MBACRM_log'.

Any ideas?

Thanks very much,

John
LVL 1
243johnmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert SchuttSoftware EngineerCommented:
Can you try it like this:
RESTORE DataBase MBACRMSQL2008R2
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAXIMIZER\MSSQL\Backup\MBACRM-SQL2000.bak'
with MOVE 'MBACRM' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAXIMIZER\MSSQL\DATA\MBACRMSQL2008R2.mdf',
MOVE 'MBACRM_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAXIMIZER\MSSQL\DATA\MBACRMSQL2008R2_log.ldf'
GO

Open in new window

0
243johnmAuthor Commented:
Thanks Robert. That error message is gone .. but now I get this:

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'MBACRMSQL2008R2' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Ideas on how to complete?

John
0
Robert SchuttSoftware EngineerCommented:
By the way, my previous comment just focuses on the syntax error, assuming your script does what you need apart from that.

Should you have other problems regarding the full text, maybe this question can help you: http:/Q_23537431.html

A quick look around the 'net suggests if restoring without FT doesn't work you may have move FT to a temp location and drop it afterwards...

I just saw your new comment while I was typing so I'll have a look now ;-)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Robert SchuttSoftware EngineerCommented:
I think you just need to use the REPLACE option.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert SchuttSoftware EngineerCommented:
PS: or drop the target database first, assuming that's what you want to do. Be careful as mentioned on the 'replace' page I linked to, not to overwrite something that you still need...
0
243johnmAuthor Commented:
Can't get that replace option to work, and there's not much guidance I could find... Finally found this link: stackoverflow.com/questions/14040983/how-to-restore-bak-file-in-sql-server-2008-r2

That led me to use this:

RESTORE DataBase MBACRMSQL2008R2
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAXIMIZER\MSSQL\Backup\MBACRM-SQL2000.bak'
with REPLACE,
MOVE 'MBACRM' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAXIMIZER\MSSQL\DATA\MBACRMSQL2008R2.mdf',
MOVE 'MBACRM_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAXIMIZER\MSSQL\DATA\MBACRMSQL2008R2_log.ldf' 
GO

Open in new window


But it also failed!

Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (29285:1953383791) in database "MBACRMSQL2008R2" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I'll have to start a new one for that error message!

Thanks again Robert,

John
0
Robert SchuttSoftware EngineerCommented:
That looks a lot like your original message. So I'm afraid the other issues were only derived problems after changes to the restore command.

There's a lot of forum discussions about this error, possibly:
* the backup is really corrupt, you can double check that on the source system with a "restore ... verifyonly"
* copying (over a network?) is corrupting the file, rare but not impossible, try comparing the files and/or running a verified copy maybe robocopy
* restoring without FT causes the error?? try my earlier suggestion to restore FT to a temp location
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.