SQL upgrades from hell - how to rollback from a botched upgrade that left server/dbs in inconsistent state?

Question

SQL upgrades from hell - how to rollback from a botched upgrade that left server/dbs in inconsistent state?

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
May 31, 2012 at 9:27 am

#256804

I wanted to take a pulse of how people approach this.
We have had some really bad experiences recently from SQL-2008 service-pack upgrades that did not go to completion, due to some obscure config issue, and left our server and databases in inconsistent/suspect state.
We had taken copies of the system-db data and log files, as well as the folders with the SQL binaries.
We had also taken backups of all users dbs prior to the upgrade.
However, we had forgotten to take a dump of the registry - "Microsoft SQL Server" node.
As a result, we were not able to fully rollback the upgrade.
What measures do people take to protect their systems from botched SQL upgrades?
Do backups of the registry, binaries and databases suffice?
Anything else that would be of value to back up?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

Marios Philippopoulos (5/31/2012)
We had taken copies of the system-db data and log files, as well as the folders with the SQL binaries.
We had also taken backups of all users dbs prior to the upgrade.

Thats mostly all you should need.

You wouldnt need the binaries or the registry, in most cases this is massively problematic

The usual way is to uninstall SQL, reinstall and patch back up to the correct level then restore databases, jobs and logins

Consider restoring master and msdb in this case

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 2

MysteryJimbo (5/31/2012)
The usual way is to uninstall SQL, reinstall and patch back up to the correct level then restore databases, jobs and logins
Consider restoring master and msdb in this case

That's what I would do. Any system databases will need to have been backed up on the exact version that you're restoring them to.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

I would go further, if this was a VM (some of mine are) then I would take a snapshot of the VM beforehand so I could restore the snapshot if things go wahoonie shaped.

On a physical I would do a server state backup (which should capture registry info amongst other info) then database backups inc. system databases. If it all went wrong, uninstall SQL Server, restore system state backups, reinstall SQL Server, attach all DBs and restore / rebuild master and msdb databases.

Certainly not an easy job to do and especially not under what we would term a 'P1' incident (ITIL, 4hr SLA with 15 min updates).

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

Thank you all for the responses.

Certainly VMs present an advantage in that sense.

Regarding physical servers, I still think it is preferable to copy the binaries, registry (under SQL node) and system-db "live" files to a "safe" location - along with db backups - and overwrite botched files in case of upgrade failure.

This has worked for us in the past.

The alternative, doing a full uninstall/reinstall and server/db rebuild, will take much longer and be much more problematic, in my view.

Imagine the poor DBA having to work on this all night to bring the server up to par to the state prior to the upgrade.

The thought sends shivers down my spine... 🙂

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 5

I like your thinking but would argue that unlike restoring a VM snapshot, replacing large chunks of the binaries, the physical data files, the registry poses more of a risk than a normal rebuild and restore. For example there is more potential for error, as you stated in your original post you hadn't backed up the registry hive for SQL Server. Your approach has more gotchas than a structured rebuild.

And yes the rebuild approach takes longer and will probably involve more late night pizza and Pro Plus (merkin translation: No-Doz?) for the DBA but at the end of it the rebuild is complete, thoroughly tested and he/she has probably sorted out many minor issues along the way.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 6

I like your thinking but would argue that unlike restoring a VM snapshot, replacing large chunks of the binaries, the physical data files, the registry poses more of a risk than a normal rebuild and restore. For example there is more potential for error, as you stated in your original post you hadn't backed up the registry hive for SQL Server. Your approach has more gotchas than a structured rebuild.

And yes the rebuild approach takes longer and will probably involve more late night pizza and Pro Plus (merkin translation: No-Doz?) for the DBA but at the end of it the rebuild is complete, thoroughly tested and he/she has probably sorted out many minor issues along the way.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

dan-572483 SSCoach Points: 15870 More actions · Answer 7

A couple of years ago a bad SQL service pack upgrade on a 2-node SQL 2005 cluster led to both nodes having to be reformated and rebuilt from the OS up, and a few 15 hour workdays with no more than 4 hours of sleep in between.

After that, I would suggest making bare-metal image backups of the servers before a service pack install.