Semi-Annual archiving of SQL Server database

Question

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

Answer 1

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?

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.

Answer 2

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.

Pros:

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

Answer 3

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