Semi-Annual archiving of SQL Server database

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.)

Restore From Backup --> [Drop Unused Tables]

• Perform a full backup of database ProductionDb.

• Restore from backup to database ArchiveDb2011B.

• [optional] Drop unnecessary tables.

• 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?

Pros:

• 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.

StackExchange Database Administrators Q#12155, answer score: 3