We have a data mart fed from an upstream data warehouse that we generate reports out of for external parties. I have a business requirement to preserve the state of the database for each month end to ensure reproducibility of results in case we are asked to do more analysis on the same time period. Database is <100GB, about 40-50 tables, nothing too crazy.

I know there are some interesting new options in SQL 2016 SP1 to consider. My goals here are less about quick/easy access to prior months and more about integrity/immutability of the data, but any solution that covers the latter while coming out ahead in the former wins the round.

Which approach makes the most sense here?

  1. Traditional backups: Archive to external storage, restore as needed. But this is clearly the most hassle from the DBA side, and the least self-service option. Note: Since I need to keep around all the copies, after while this will be a lot of storage space.
  2. Database Snapshots: Create snapshots and keep indefinitely. But I'm not aware of any solution that lets me safeguard the snapshot itself, from what I've if there is any corruption on the server(s) then your snapshots are hosed and there is no way to reproduce them. But the self-service aspect looks hard to beat. Is there a 3rd party solution I'm missing?
  3. Temporal Tables: The cool new feature of 2016, is anyone actually using this? Refactoring our scheme to support this looks like a heavy lift, so maybe this is off the table.
  4. Based off of @Max Vernon's suggestion...Monthly Restores to Read-Only DBs, maybe using Stretch Database: This sounds pretty good as this will be a lot of cold data....buuut is anyone actually using Stretch Database? Is this something Microsoft will quietly deprecate in a couple of years?
  5. Something else I'm totally missing here?

Update: As of mid-2019, Stretch Database has a pretty high cost to get off the ground, starting at about $1.8K/mo just for compute in Azure (storage extra) so I would say this puts it out of reach for all but the larger use cases.

| improve this question | | | | |
  • How long does your Business intend to keep the data or when does it become stale? different strategies flow out of that. Vernon’s idea is a straightforward way of keeping everything without making it too complicated. On the other hand, you could also use a combination FOR STORING HISTORIC DATA if you need to make time comparisons easier (avoiding UNION ALL) – clifton_h Jun 5 '19 at 13:35
  • @clifton_h let's say 8 to 10 years of monthlies. So let's say 120 database copies... – cmcapellan Jun 5 '19 at 14:43
  • Why not combine both 1 & 2? – dean Jun 10 '19 at 10:15
  • @dean All my reading on snapshots makes me leery for this use case. From what I understand, even if I have full backups, I can't recreate a snapshot if it is corrupted or needs to be moved to a new server. A restore only restores the database itself, not snapshots. Someone please correct me if this is not the case! – cmcapellan Jun 11 '19 at 16:22
  • Correct. The idea was to both create monthly backups and the snapshots, so you have a failback option. And I wouldn't keep the snapshots for too long anyway. – dean Jun 12 '19 at 9:14

Since you're dealing with a single database (or a known set of databases), simply create a SQL Server Agent Job that restores the month end backup to a "month-end" database.

Schedule the job to occur early on the first day of each month. The job would simply restore "last nights" full backup. You are taking nightly backups, right?

In order to ensure the immutability of the month-end data, simply mark the database as read-only after the restore finishes. Something like this:


Details for the above command are here.

| improve this answer | | | | |
  • Thanks @Max, this was what I was thinking except that I will end up having dozens of monthly copies of the DB once we're 2-3 years in. Was hoping one of the other solutions would be a bit more elegant... – cmcapellan Jun 5 '19 at 2:10
  • If you store the archival data on a SAN that does data-deduplication it'll likely be efficient enough. – Max Vernon Jun 5 '19 at 10:50
  • 1
    Even if you don't have a SAN with dedupe, storing the archived databases on cheap SATA drives might be a simple way to keep the database copies you need without exorbitant cost. – Max Vernon Jun 5 '19 at 12:20
  • 1
    So I talked to the business, they are fine with offline backups for the short term but want us to work on some temporalized version of the key tables. – cmcapellan Jun 8 '19 at 0:47

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.