troubleshooting Question

Problem restoring a replicated SQl2000 database to a SQL2005 Server

Avatar of cquinn
cquinnFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
15 Comments5 Solutions1337 ViewsLast Modified:
We have a procedure in place to restore a copy of our main database (called CIMMS, running on a SQL2000 server) onto our SQL2005 server to allow our salespeople to run reports against the data without impinging on the main database.  A backup copy of the 2000 database runs overnight and this is then restored onto the SQL2005 server under a different name (RSCIMMS)

The 2000 database is replicated to allow some external users access to a subset of the database to enter orders online.

The package has been running successfully for months, but has suddenly stopped working - it appears that the restore works, but the automatic removal of the replication information seems to fail, leaving the database offline.  Every morning I have to VNC onto the server and run a script to bring the database online in single user mode, apply certain permissions to allow the report users access, then shrink the database and rebuild the indexes, before setting the database to multi-user and allowing the sales staff access.  Restoring the database to another SQL2000 server works fine, though restoring to a different 2005 server also fails

The output from the package is as follows
Executing the query "RESTORE DATABASE RSCIMMS
FROM DISK = 'E:\Backups\Brazil\BRAZIL_CIMMS_DEVICE.BAK'
WITH REPLACE, RECOVERY,
MOVE 'CIMMSData' TO 'D:\Data\RSCIMMS_Data.MDF',
MOVE 'CIMMSLogs' TO 'D:\Logs\RSCIMMS_Log.LDF'

" failed with the following error: "Invalid column name 'lightweight'.
Database 'RSCIMMS' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.
RESTORE could not start database 'RSCIMMS'.
RESTORE DATABASE is terminating abnormally.
Processed 1575592 pages for database 'RSCIMMS', file 'CIMMSdata' on file 1.
Processed 7 pages for database 'RSCIMMS', file 'CIMMSlogs' on file 1.
Converting database 'RSCIMMS' from version 539 to the current version 611.
Database 'RSCIMMS' running the upgrade step from version 539 to version 551.
Database 'RSCIMMS' running the upgrade step from version 551 to version 552.
Database 'RSCIMMS' running the upgrade step from version 552 to version 553.
Database 'RSCIMMS' running the upgrade step from version 553 to version 554.
Database 'RSCIMMS' running the upgrade step from version 554 to version 589.
Database 'RSCIMMS' running the upgrade step from version 589 to version 590.
Database 'RSCIMMS' running the upgrade step from version 590 to version 593.
Database 'RSCIMMS' running the upgrade step from version 593 to version 597.
Database 'RSCIMMS' running the upgrade step from version 597 to version 604.
Database 'RSCIMMS' running the upgrade step from version 604 to version 605.
Database 'RSCIMMS' running the upgrade step from version 605 to version 606.
Database 'RSCIMMS' running the upgrade step from version 606 to version 607.
Database 'RSCIMMS' running the upgrade step from version 607 to version 608.
Database 'RSCIMMS' running the upgrade step from version 608 to version 609.
Database 'RSCIMMS' running the upgrade step from version 609 to version 610.
Database 'RSCIMMS' running the upgrade step from version 610 to version 611.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

PackageEnd,CIREPORTS,CIDOMAIN\CIreports_agent,CopyCIMMS,{12D016FF-F1F3-4036-B844-A3E024663785},{9D6645EB-EAD2-41C3-A193-65C9E7BA057A},19/07/2008 02:10:37,19/07/2008 02:10:37,1,0x,End of package execution.

I have researched the error code (the only place on the whole wide web that it has been mentioned before is http://www.sqlservercentral.com/Forums/Topic359797-357-1.aspx#bm360783) but am none the wiser.  The MSDN page does not offer much help.

I am a bit of a newbie in the DBA world - our previous DBA left recently and I have been dropped into the role with no training and little experience, so be gentle with me!

Can anyone help?
ASKER CERTIFIED SOLUTION
EugeneZ
SQL SERVER EXPERT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 5 Answers and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros