2

I have a database that is not going to be used anymore but that needs to be kept around for a long time. I'd like to remove it from the production system. My goal is to save storage space when archiving that database. I need to preserve it at full fidelity, so a BCP export is not good enough. I also would not trust generated SQL scripts to preserve it fully.

Two options:

  1. Take an uncompressed backup and compress it using 7-Zip.
  2. Detach the database and compress the MDF and LDF files using 7-Zip.

Is option 2 safe? Which one is preferable? What are important considerations here?

  • 2
    No, do not go route #2. Take a backup and after taking the backup, verify it. When you detach, you don't know whether the database was corrupt at that point in time. If it was, you may be toast attempting to re-attach it – billinkc May 24 '15 at 13:59
  • 4
    Don't use detach. blogs.sqlsentry.com/aaronbertrand/bad-habits-file-backups Also if you're on standard or better, use the native backup compression - I suspect you won't find a huge gain from 7-Zip on top of that (at least not commensurate with the additions time taken to compress/decompress). – Aaron Bertrand May 24 '15 at 14:57
  • 2
    Okay, still, my main point was: STOP thinking a detach is a backup. IT'S NOT. – Aaron Bertrand May 24 '15 at 22:46
  • 1
    @Greg I can assure you that space usage is an issue (I assume you mean space by "performance"). I will store multiple copies of the archived data. This is required to prevent bit rot, no matter which of the methods given above I use. – usr May 26 '15 at 21:59
  • 1
    i misunderstood then, i thought speed being 50x worse was an issue. – Greg May 26 '15 at 22:07
4

You can save the index creation DDL statement and delete the indexes, to save more space before any archival path you choose. There's more advice on shrinking and fill factor here: http://www.brentozar.com/archive/2010/02/how-to-really-compress-your-sql-server-backups/

I've used option 2 to get better results than with the SQL built-in backup compression. Always start with DBCC CHECKDB ! Also take into consideration that your restore time will go up if you go on this path, as it takes a while to (de)compress with 7-Zip's best compression settings.

|improve this answer|||||
1

To be sure you have a backup you can restore and use directly I would:

  1. Take a native full backup with options Verify backup when finished and Perform checksum before writing to media and Compress backup
  2. Test the backup; restore to test instance and perform DBCC CHECKDB
|improve this answer|||||
  • Which option is better though? I do understand that I can take a backup. – usr Nov 24 '15 at 14:17
  • @usr I think you got enough confirmation in the comments about which solution is better. – dezso Nov 24 '15 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.