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?

Improve this question
7
  • 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
    Commented May 24, 2015 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
    Commented May 24, 2015 at 14:57
  • 2
    Okay, still, my main point was: STOP thinking a detach is a backup. IT'S NOT.
    – Aaron Bertrand
    Commented May 24, 2015 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
    Commented May 26, 2015 at 21:59
  • 1
    i misunderstood then, i thought speed being 50x worse was an issue.
    – Greg
    Commented May 26, 2015 at 22:07

2 Answers 2

Reset to default
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
2
  • Which option is better though? I do understand that I can take a backup.
    – usr
    Commented Nov 24, 2015 at 14:17
  • @usr I think you got enough confirmation in the comments about which solution is better.
    – András Váczi
    Commented Nov 24, 2015 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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