How will you back out if your change doesn’t work?! Always take a backup! There should always be a back out script to go with any change script! These are things that have been drilled into me time and time again. Sometimes by management, sometimes by bitter experience. If I’m changing a piece of code I make sure that I have a copy of the old version either in some sort of versioning software or worst case in a text file in a known location (at least to the other DBAs). If I’m changing data I make sure I have a current backup (if possible) or a copy of the data I’m changing. You will probably realize that I’m talking about Operational Recovery and in fact the beginnings of this post were the inspiration for my post last week “Disaster Recovery’s big brother Operational Recovery”.

Here was my situation a few weeks ago. I’m updating a relatively small amount of data, around 2-3000 rows of several million. Normally I would do at least a transaction log backup to mark the time of my change but in this case the database is set to simple recovery.

If the database is set to simple recovery then in the strictest sense your business has said that they are ok with losing everything back to the last full backup and we don’t care about backing up changes. (Please note, if you’re business has NOT said they are ok with losing everything back to the last full backup then you had better reconsider your backup/recovery strategy. Or start working on your resume. You might read “The 9 Letters That Get DBAs Fired” by Brent Ozar to help you make your decision.)

In our case the business has said they are ok with simple recovery and understand its implications. So if something goes wrong with my change, in theory, I can just pull up the last full backup and restore. In reality it rarely works that way. The business agreed that if there is a true full-blown disaster they could live with the loss of data. This however was not a full-blown disaster. This was me putting in the wrong WHERE clause on my update or something similar. Guess what? They want things back the way they where, and no data loss please.

So no transaction log backup available, but I have to back up my change. A differential backup is a thought but will be too large (200+gb at that point) and a full is completely out of the question (500gb). I should point out we have plenty of space for our normal backups, just not enough to throw an extra 500gb backup file out there every time I’m making a change. So what should I do? I’m going to back up just the rows I’ve changed. Historically I would do something similar to this:

SELECT BusinessEntityID, SickLeaveHours, HireDate
INTO HumanResources.Employee_20130626_080000
FROM HumanResources.Employee
WHERE HireDate < '1/1/1999'
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours + 5
WHERE HireDate < '1/1/1999'

I’m sure several of you at least have noticed that if a row get’s added between my INSERT INTO statement and up UPDATE statement I’m messed over. So the simple solution here is to wrap them both in a transaction. This will work, but I’m not terribly happy about the length of the transaction. I’m basically running the same query twice. I would rather something smaller. Well, using the OUTPUT clause I have an easy way to do that.

SELECT TOP 0 BusinessEntityID, SickLeaveHours AS Old_SickLeaveHours, 
SickLeaveHours AS New_SickLeaveHours, HireDate
INTO HumanResources.Employee_20130626_080000
FROM HumanResources.Employee
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours + 5
OUTPUT inserted.BusinessEntityID, deleted.SickLeaveHours AS Old_SickLeaveHours,
inserted.SickLeaveHours AS New_SickLeaveHours, inserted.HireDate
INTO HumanResources.Employee_20130626_080000
WHERE HireDate < '1/1/1999'

It doesn’t look much shorter, but that is because the first statement is creating an empty “storage” table. In terms of time and performance it’s basically a null statement. It checks the schema of the table(s) selected from and creates a new table. The meat of the operation is the second query. Both update and back in the same statement. Nice and simple, no chance of new records being inserted during my transaction, which is about as short as possible, and I have all the data I need to undo my work.

Filed under: Backups, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: backups, brent ozar, code language, disaster recovery, language sql, microsoft sql server, sql statements, T-SQL