What is a dirty backup?
When I’m developing code for resolving data problems, I can’t always restore the dev database from a production backup. Other people have code in Dev, have changed data, etc. So before I start mucking about with the database, I employ a few quick and dirty tricks to protect myself in case of disaster. I call these tricks my “dirty backups” because they can only protect me from my own bad code, and not from a true database disaster.
The first trick is the use of the Database Snapshot. I love this little tool. Snapshots enable me to preserve the state of the database As Is. I can have as many snapshots as I need, create a new one every time I hit a major success point. It’s like the Save feature on a video game, or the Save Draft on an email / blog post. I simply run the below bit of code, changing the snapshot names so I know which one contains which data, and then when I need to restore, I have my own mini-Restore-Point-In-Time without losing the majority of other people’s changes.
The code is literally this simple:
CREATE DATABASE MyDBSnapshotName ON
( NAME = 'MyDBFileName', FILENAME =
'C:\PathWhereIWantDBSnapshotFile\MyDBSnapshotFileName' )
AS SNAPSHOT OF MyDB;
OR
CREATE DATABASE MSDB_SS ON (NAME='MSDBData',FILENAME='C:\MSDBSS.snap')
AS SNAPSHOT OF MSDB;
Interesting note, MASTER will not allow you to snapshot it, but MSDB will.
My other dirty trick is the use of the SELECT…INTO code.
At my workplace, we have a database called DB_User used to store pseudo temp-table data. The transient nature of temp tables means they are a poor choice for temporarily backing up individual tables during development. For instance, if I want to make a modification of certain records in an inventory data table, the temp table used to backup the “current state data” would be destroyed the instant I logged out of my session. And if the end user came to me during QA testing and said “you messed this up”, I can’t go back into that temp table and look at what data existed prior to my table update. SELECT…INTO allows me to temporarily backup these few tables as temporary permanent tables for research, troubleshooting, and fixing (if necessary) during both development and QA testing.
The bonus to this trick is that I don’t have to restore the entire database (and lose the fixed data) to see the original state, and I don’t have to disrupt anyone else’s testing for a full database restore either. I can simply use an INNER JOIN on the table key to restore all the table’s data to it’s pre-UPDATE state.
In addition, the use of a database solely for temporary permanent tables allows SELECT…INTO to be used without cluttering the real database with meaningless tables and redundant data.
These are my dirty backup database tricks. Do you have any? I’d love to hear about them if you do.
