July 22, 2008 at 6:47 am
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 (this site seems to be the only place on the whole wide web that it has been mentioned before 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?
July 22, 2008 at 2:51 pm
If you are restoring it to a different Server, then I would expect the replication to be dropped.
Is there replication setup on the Server where Sales runs the reports from? Are all of the replication jobs named the same on the production Server as well as the Server used for reporting?
You could try dropping all of the replication jobs before running the restore and then recreating them after the restore has completed. However, as stated before, if you copy a replicated database from one Server to another, there should be no problems with Replication since the jobs are invalid on the other Server.
Regards, Irish
July 23, 2008 at 3:36 am
There is no replication set up on the target server
July 25, 2008 at 3:30 am
Further investigation has shown that when I restore the database to another SQL2000, there are problems there with removing the replication objects (triggers), though this is not reported during the restore.
Running the sp_removedbreplication system stored procedure against the database on the SQL2000 server reports that it has run successfully, though the triggers are not removed. To get the restored database into a workable status I have to remove these triggers manually.
It therefore suggests that the source database is in some sort of inconsistent state. I think I'll try removing replication from this totally (no big problem as the replication's not working properly at the moment and the agent jobs have been disabled) -AFTER I've backed it up of course!
July 30, 2008 at 6:02 pm
As you are restoring between versions (i.e. 2000 to 2005) try the following command:
[font="Courier New"]sp_restoredbreplication @srv_orig='SVR2000NAME', @db_origin='dbName', @perform_upgrade=1[/font]
The @perform_upgrade parameter is marked as for internal use only in BOL, but this worked for me and you can then run sp_removedbreplication afterwards.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply