[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 567
  • Last Modified:

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
0
243johnm
Asked:
243johnm
  • 5
  • 2
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Robert SchuttSoftware EngineerCommented:
I think you just need to use the REPLACE option.
0
 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now