asked on Problem restoring a replicated SQl2000 database to a SQL2005 Server
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_
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,CIDOM
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?
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_
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,CIDOM
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?
Microsoft SQL ServerMicrosoft SQL Server 2005
Last Comment
EugeneZ
Sounds to me like you're missing a column in a table on your destination.
ASKER
It does a complete restore on the target server, so all tables are overwritten.
The problem occurs even if I drop the target database completely, then create a new, empty database with the same name. None of the tables has the column name Lightweight - I assume that this is a system object
The problem occurs even if I drop the target database completely, then create a new, empty database with the same name. None of the tables has the column name Lightweight - I assume that this is a system object
Is your source server running in full recovery mode?
search your RSCIMMS DB tables for column name 'lightweight' and check for some additional characters (like tab, etc)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
It's at SP2 already
9.00.3054.00 SP2 Standard Edition
9.00.3054.00 SP2 Standard Edition
tell please more about: step-by-step " the automatic removal of the replication information seems to fail that is leaving the database offline."
ASKER
When you restore a replicated database onto a different server that the one on which it originates, SQL Server will remove any replication, leaving the new copy of the database as a standalone database.
The scheduled job that runs the restore also then applies some new database users and then shrinks the database.
The restore part of the job completes - all of the data is loaded onto the target server, but the system procedures that remove the replication (done automatically by SQL Server) fail with the message about column "lightweight" - this failure leaves the database Offline and the rest of the job then does not complete.
To get the database up again, I have to set it Online (in single user mode), run the scripts to add users and shrink the database, then set the database to Multi-user.
Attempting to run the sp_removedbreplication stored procedure directly, as suggested in the MSDN page, fails with the same error
http://msdn.microsoft.com/en-us/library/ms151782.aspx - which leads to -
http://msdn.microsoft.com/en-us/library/ms188734.aspx
The scheduled job that runs the restore also then applies some new database users and then shrinks the database.
The restore part of the job completes - all of the data is loaded onto the target server, but the system procedures that remove the replication (done automatically by SQL Server) fail with the message about column "lightweight" - this failure leaves the database Offline and the rest of the job then does not complete.
To get the database up again, I have to set it Online (in single user mode), run the scripts to add users and shrink the database, then set the database to Multi-user.
Attempting to run the sp_removedbreplication stored procedure directly, as suggested in the MSDN page, fails with the same error
http://msdn.microsoft.com/en-us/library/ms151782.aspx - which leads to -
http://msdn.microsoft.com/en-us/library/ms188734.aspx
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
The replication is there to replicate the data to an entirely different server, for extranet orders
The removal of the replication is done automatically by the SQL 2005 server - which is neither the publisher or subscriber - the system is set up to have a copy of the live database available for reporting purposes, but up to 24 hours out of date.
The replication on the 2000 server is as follows:
The publisher is on a third server (Baghdad) and merge replication is set up between this server and two other copies (Extranet and BRAZIL) - the extranet database and Publisher database contains a subset of the main database (new order tables, client specific data etc) - when a client places an extranet order, the data is replicated onto Baghdad, and then on to Brazil to make the data available to our admin staff for processing. Any changes to the master copies of the client data etc on Brazil are replicated back to Baghdad, then on to the Extranet server, allowing the extranet clients to see these changes.
The Brazil database is backed up nightly, then restored onto our reports server (CIReports) which is a SQL 2005 server - all of the others are SQL 2000. As I said previously, when restoring a database onto a different server, SQL automatically removes replication information, and this process is failing
The removal of the replication is done automatically by the SQL 2005 server - which is neither the publisher or subscriber - the system is set up to have a copy of the live database available for reporting purposes, but up to 24 hours out of date.
The replication on the 2000 server is as follows:
The publisher is on a third server (Baghdad) and merge replication is set up between this server and two other copies (Extranet and BRAZIL) - the extranet database and Publisher database contains a subset of the main database (new order tables, client specific data etc) - when a client places an extranet order, the data is replicated onto Baghdad, and then on to Brazil to make the data available to our admin staff for processing. Any changes to the master copies of the client data etc on Brazil are replicated back to Baghdad, then on to the Extranet server, allowing the extranet clients to see these changes.
The Brazil database is backed up nightly, then restored onto our reports server (CIReports) which is a SQL 2005 server - all of the others are SQL 2000. As I said previously, when restoring a database onto a different server, SQL automatically removes replication information, and this process is failing
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for all of the suggestions - unfortunately none have helped cure the problem
what is status of your problem? if it is still there - and nothing helps -open the Microsoft PSS case
http://support.microsoft.com/
http://support.microsoft.com/