asked on

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:

https://www.experts-exchange.com/questions/23966944/Restoring-Database-Error.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

Robert Schutt

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

243johnm

ASKER

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

Robert Schutt

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 ;-)

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Schutt

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...

243johnm

ASKER

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

Robert Schutt

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