Monday, March 05, 2012 9:38 PM
I am planning a SQL 2000 to SQL 2005 inplace upgrade. what is the rollback for Inpalce upgrade? Does taking an image of the database server help with the rollback purpose? My understanding for rollback in Inplace upgrade was to reinstall SQL 2000 from scratch.Please advice.
Monday, March 05, 2012 10:17 PM
First thing you must always do when doing upgrades is take a full backup of all the databases. Regarding to the rollback plan you should consider below options
1. - Have another server ready with SQL 2000 already installed to restore the databases in case something goes wrong with the upgrade
2.- Uninstall the components of failed upgrade , install SQL 2005 from scratch and restore the databases (you need more time to accomplish this)
In my case, I try to get a stand by server or at least a sql instance on another server in case I have to do an emergency rollback
Also consider side-by-side upgrade in case you have the resources
Tuesday, March 06, 2012 2:57 PM
Thanks Javier. I my case i do not have a test server and the application team wants the upgraded instance to have same name as the current default instance. They are not willing to make any changes from application side for the upgrade. I just wanted to confirm the rollback strategy so that i can make the application team aware of the consequences of the inplace upgrade.
Wednesday, March 07, 2012 7:10 AMModerator
Hi SQL greenhorn,
Rollback: For many database systems in production, it is impossible to justify a change without a rollback strategy in case the results are not acceptable. The side-by-side upgrade strategy supports rollback at the time of acceptance testing because the legacy instance can still be made available. However, after users update the databases in the new instance, rollback might no longer be feasible.
Develop a recovery plan. Develop upgrade rollback procedures in case of an upgrade interruption. The recovery plan should include running a DBCC consistency check on the pre-upgrade databases before backup as well as a full restore of the database to validate the backup reliability. After the upgrade, you should also perform a consistency check and a backup with validation. Make sure you test your rollback procedures.
Reference: SQL Server 2005 Upgrade Technical Reference Guide:
Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
- Marked As Answer by Maggie LuoMicrosoft Contingent Staff, Moderator Wednesday, March 14, 2012 3:05 PM