Retiring a Database

Every dog has it’s day, and every database has it’s time. Maybe a “shelf life” would be more appropriate. But with data retention times getting longer and longer, some databases must be kept around long after their applications have been retired. So what’s a good way to retire a database?

I have a set procedure that I use for retiring a database, once it’s application has no longer been used. There is some ongoing maintenance, but that’s the price you pay for data retention. If you need to put a DB in cold storage, here’s one way.

Backup – Set the recovery model of the database to SIMPLE and take a backup of the existing database so you have a starting point to go back to, should anything go wrong with this procedure.  I’ve never had to use that backup in my time, but you never know. It pays to be cautious.  We’ll need the DB to be in SIMPLE mode since the next thing we’re going to do is…

Shrink – Yes, I know that’s considered a dirty word by many data professionals, but if we understand what shrink does, and are ready to mitigate any of the problems it presents, then we can shrink a database safely.  In this case, we’re taking the database out of production and storing it.  Disk space just became far more important than performance. So, we can go ahead and shrink the database as small as we can get it.

Optimize – Now that we’ve shrunk the database, we’re going to have loads of internal fragmentation going on.  At this point, we’re going to re-index everything and update all statistics, so that when we do restore this database, it will be ready to read, quickly. Hey – I said that performance wasn’t as important as disk space. I didn’t say it wasn’t important at all. :-)

Truncate – You’ll notice (hopefully) that after the re-index / update, our DB size has grown again. We still want to save some disk space, so let’s shrink the data file again, but this time, using TRUNCATE_ONLY.  All we want to do is remove the empty space at the end of the file. This may not net us much additional space, if any, but when it comes to storage, every byte counts. For the log file, we’ll go ahead and shrink it as far down as it will go.  Since it’s not storing data, we don’t need it.

SET to READ_ONLY – When and if we do restore this database, we’re probably going to want to avoid accidentally updating the data, since it’s being held for archival purposes. If we really do need to deliberately update the data in this database, we can set it to READ_WRITE after we restore it.  If required, we can then follow this procedure again to archive that copy of the database.

Backup Again – This is the most critical part of the process. Now that we have a good copy of the database, we want to back it up to the most up-to-date version of SQL Server in our shop, as well as storing it on the most recent hardware we have, regardless of the media type. This may involve taking backups, restoring them to other, more updated servers, and backing up again. 

Restore – TEST THAT BACKUP. The only way you truly know if a backup is good is to restore it. Also, as part of our routine DR exercises, it’s a good idea to update that backup to the most recent version of SQL we have, and the most recent storage hardware as well. With that, you should be able to keep your data around indefinitely. 

Why not just take a backup, put it on a removable disk and be done? Imagine it’s 10 years later. The database we need is backed up to media we can’t read because the hardware died 4 years ago, and “nobody makes those drives anymore”. Don’t laugh. I have seen this very failure. I’ve also seen situations where we can read the media, but our backup format is so old that our newer software can’t read it. We have to go scrambling for a (sometimes unlicensed) copy of the older software. Don’t let this happen to you. Having redundant copies, i.e., a backup of the backup, is a great idea as well. Avoid single points of failure. Oh, and we are using backup compression, right? :-)

One final caveat to this: if the DB we are restoring uses features that get deprecated, we’ll want to know, so a good suggestion is to use the TRY/CATCH restore method described by Shaun Stuart.  If we do have deprecated features in use, we’ll have to decide how to address those when we find them. They may or may not matter.

Thanks for reading.


6 thoughts on “Retiring a Database

  1. Shaun Stuart January 25, 2013 / 12:00 pm

    Interesting ideas. I’m not sure I agree about upgrading the database to the latest version of SQL before archiving. We have several applications that use SQL 2005. They do not support SQL 2012. If I follow your method, the archive database will only be able to be restored on SQL 2012, and therefore, could not be accessed by the owning application if I need to go back and bring it online again for some reason. And as for shrinking the database, why not save yourself some time and compress the backup file with WinZip instead of shrinking, re-indexing, then truncating? I can see why you would set it to read only, but I don’t think that’s necessary either. After the restore, you still have your original backup file that will not be changed, even if the restored database has had changes made to it.


  2. dmmaxwell January 25, 2013 / 1:17 pm


    All good points. Let me explain my reasoning a bit further. As usual, this is a method that works for me – your mileage / experience will vary. :-)

    This is my method for specifically creating a perpetual archive for data. Its not scoped for application functionality. If I had the need to preserve the application functionality as well, maybe because the database on it’s own was particularly cryptic or unwieldy, I would more likely create a specific VM for it, and archive a snapshot of that. While I have seen that necessary in a case or two, that’s rare for me.

    As far as the various compression steps, while reducing the backup size is a primary goal, a secondary goal is to reduce the space required for restore as well. So in that case, I think shrink/reindex/truncate makes sense. Yeah, it’s a bit of additional work, I agree. I think it’s worth it, though. I would assume SQL’s backup compression being used regardless. Not sure if WinZip would be of any additional benefit. Also, then I have to have a version-compatible copy of WinZip as well. Not that it would be hard to do so. And that’s a suggestion I will definitely pursue.

    The choice to set it to read only is a specific reaction to two situations I’ve been in where there were discrepancies in query results given to auditors. We had come to find out that data in the restored database had been altered in a way that invalidated the results. Not intentionally, of course, but having the database set to read only would have mitigated that. If we really need to have the database read/write, it only takes a moment to do so.



  3. Shaun Stuart January 25, 2013 / 2:03 pm

    OK. I see your point on the shrinking and read only setting (aren’t auditors a pain?). I’m still not sure I agree with you on updating the db to the latest version of SQL. Eventually the latest version will be out of date as well. Since SQL will only restore backups made 2 version prior, you only gain yourself 10-15 years doing this. (Unless you plan on updating all your archives when each new release of SQL Server comes out – and, as you pointed out, that will fail as soon as some feature is deprecated.) As you say, that may be acceptable to you, but if you are truly looking for a perpetual archive, I think you’re better off just archiving the backup and including an image of the install DVD for the appropriate version of SQL Server (or an image of the SQL Server VM).


  4. dmmaxwell January 25, 2013 / 2:34 pm


    I do make mention of testing / updating the archives as part of routine DR testing. Maybe the backup version doesn’t need to be the latest and greatest, but I do think it does need to be somewhat current and, maybe most critically, currently supported.

    As you rightly point out, some features will be deprecated, but this is true of not only the database, but SQL, applications, even the operating system as well. We seem to agree that a VM snapshot is the best course in the case where all of the above must be preserved. Usually, however, it’s the data that is of critical importance. As long as we can extract the data somehow, we can usually assemble whatever information is required.

    One other thing that can get deprecated over time is skills. I don’t know if I would rely on younger DBAs being able to work with much older versions of SQL. My experience only goes back to 6.5/7.0, and it’s sometimes a collection of hazy memories at that. :-) By keeping the backup version current, we increase the available support for it as well.

    A lot of this also comes down to retention requirements, which I should probably have touched on. 10-15 years may be enough. It may even be overkill. Maybe I should do a follow-up post with some different scenarios / concerns in it?



  5. Drummond January 7, 2014 / 3:03 pm

    Reblogged this on drummondsql and commented:
    An excellent article by David Maxwell regarding the processes to retire a database.


Please Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.