Twice a year we create a static, archived subset of our production database. The newly created database contains roughly half of the tables as the production db (log/audit tables, etc. are not copied over). Also, several records are not included (records marked as inactive, etc.)

Once created the archive db is set to read-only access. From the front-end, users are able to switch from the production db to any one of the read-only archives. The archives remain available indefinitely.

The data is in SQL Server 2008 R2. What is the best way to automate this type of situation (we've done it several different ways in the past, but have not settled on a single, straight-forward approach.)

  • 1
    Which approaches have you tried? What problems did each approach have for you? – voretaq7 Feb 2 '12 at 20:23
  • I'll list my approaches as answers below... – mwolfe02 Feb 2 '12 at 20:27
  • 1
    Those should really be part of your question, as the approaches you've tried with the pros/cons. You (as the questioner) putting them as two competing answers kinda stretches how this site is supposed to operate. – mfinni Feb 2 '12 at 20:57
  • I understand how the site operates (I'm a 5k+ user on SO), but I didn't think I could flesh out what I've tried without making the question itself unmanageably long. Plus, I'm hoping that the answers themselves will be voted up or down to reflect what the community thinks is the right approach. – mwolfe02 Feb 2 '12 at 21:08
  • @voretaq7: I forgot there's a dedicated dba site now. I have no problem if you would like to move it. Thanks for your input both here and below! – mwolfe02 Feb 2 '12 at 22:25

Restore From Backup --> [Drop Unused Tables]

  1. Perform a full backup of database ProductionDb.
  2. Restore from backup to database ArchiveDb2011B.
  3. [optional] Drop unnecessary tables.
  4. Set database ArchiveDb2011B to read-only.

Problems with this approach:

  • It uses the most disk space. Our previous archives are roughly 1GB in size. Our production db is about 4GB in size. If we skip step 3, we end up with archives four times bigger than what we typically have had.
  • Dropping tables requires first dropping any related foreign keys.
  • Triggers remain in place in the archive (the production db lives in a merge replication topology). Is this a moot point because the archive will be read-only?


  • It is relatively simple. If we skip step 3, it is dead simple.
  • Performance should be pretty good because all of the indexes remain in place. (We have missed indexes in the past using other approaches. We don't usually realize it's a problem unless we happen to overhear users complaining about how slow a particular archive is.)

We have not used this approach for archiving purposes. We have used this approach to create dev environments and it works well in that scenario. It seems like the biggest drawback is the extra disk space used. Disk space keeps getting cheaper, but it is a four-fold difference over other approaches.

  • 1
    Despite the size this is the solution I'd advocate. More specifically, I'd advocate making the backup and sticking it on a tape (or tape equivalent). If you need parts of the archive to be online (your case) restore, delete what you don't need, and perform your DBMS equivalent of a VACUUM FULL, compacting, etc. to collapse the free space and reduce the size of the data store. – voretaq7 Feb 2 '12 at 22:05
  • 1
    I'd go for this one aswell.. if space is a issue - look into cheap raid6 s-ata or something similar (I'm guessing IOPS isnt critical for the read only archives) – pauska Feb 2 '12 at 22:11
  • 1
    @pauska has a good point: as the archives are read-only even cheap/slow disk shouldn't be too painful - It's not like the disk will have to bounce back and forth between the table and a transaction log. – voretaq7 Feb 2 '12 at 22:19
  • This approach seems the safest to me since it will encompass schema changes that might break scripts to create objects and move data to a new database and it will not have as large of an impact on the production database directly, just the disk. And as @pauska said this could be mitigated with separate, cheaper storage. – Jason Cumberland Feb 3 '12 at 15:51

Create Table Structure --> Fill Tables --> Add Security

  1. Use SSMS to generate scripts to create table structures.
  2. Change the USE [ProductionDb] at top of each script to USE [ArchiveDb2011B].
  3. Create the archive DB.
  4. Run the script to create the tables, indexes, foreign keys, etc.
  5. Populate the archive db by running a series of INSERT queries sandwiched between SET IDENTITY ON/OFF statements.
  6. Add the appropriate security to the db.

There are a few issues with this approach.

  • I think there are too many steps.
  • You need to be careful about what order the INSERTs are performed to maintain referential integrity.
  • It requires too much manual intervention. We are using the free edition of SSMS to generate the script, so we cannot save the options we use for when we generate the scripts in 6 months.


  • Allows a great deal of flexibility.
  • Minimizes storage space in the archive db because we only copy exactly what we want.

This is what we tend to do most of the time. It usually involves at least three or four moments during the process where we stop and say, "Oh, that's right, we were supposed to change this setting" or "We forgot to do that other thing first".


Use Database Snapshots

Use SQL Server's built-in Database Snapshot functionality. In the article on Typical Uses of Database Snapshots, the following is listed as a common use case:

Reasons to take database snapshots include:

  • Maintaining historical data for report generation.
    Because a database snapshot provides a static view of a database, a snapshot can extend access to data from a particular point in time. For example, you can create a database snapshot at the end of a given time period (such as a financial quarter) for later reporting. You can then run end-of-period reports on the snapshot. If disk space permits, you can also maintain end-of-period snapshots indefinitely, allowing queries against the results from these periods; for example, to investigate organizational performance.

This describes exactly what we want to do. However, I have some serious concerns about the implementation. In the article, Limitations and Requirements of Database Snapshots the following drawbacks (among others) are listed:

  • Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
  • Database snapshots always work on an entire database.
  • If a database snapshot runs out of disk space, it must be deleted (dropped).

That last one is a show stopper. The production database gets moderate use and we are looking to maintain the archives for 10+ years at least (at two per year).

We have never actually tried this approach, but it sounded intriguing (at least initially). However, this functionality seems to be geared more towards temporary or rolling snapshots, not indefinite use (though the use case statement specifically says indefinite use is possible).

  • I would definitely not go with snapshots -- In my (admittedly limited) SQL Server experience these are basically "super-transactions" and aren't suitable for any kind of long-term use. They're great prior to major schema changes, but you don't want the snapshots hanging around any longer than necessary... – voretaq7 Feb 2 '12 at 22:24
  • I came to the same conclusion. The described use case seemed to be a perfect fit, but the devil seemed to be in the (implementation) details. I think if I went with this I would lie awake at night wondering if my db was corrupting... – mwolfe02 Feb 2 '12 at 22:34

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.