August 2, 2012 at 7:45 am
Ok, so I have been tasked with a seemingly simple operation. :hehe: DatabaseA is used for a number of different applications. Fundamentally, the applications are all the same in the sense that they all do the same thing, but each customer has a 1 to many relationship to these "app_id's." The goal is to take a nightly "snapshot" that will can be used to completely overwrite an app_id's related data. I will continue to refer to this as the "snapshot," but understand that it is not a snapshot as SQL Server defines it.
We are using Standard edition. My current thought is to run a nightly job to populate 1 de-normalized table with all of the data and columns needed for an overwrite of data with a "snapshot_date" column. When needed, a second stored proc can be run that will accept an app_id and simply run a full backup, delete all existing data in the production tables for the app_id, and then insert all data from that app_id from the "snapshot."
I thought this would be better than having 55 separate snapshot tables. Any thoughts on why this may or may not work and considerations? The restore operation would not be a common thing, this is basically a piecemeal restore of data that can be initiated with the press of a button. Space is currently not an issue.
Jared
CE - Microsoft
August 2, 2012 at 8:25 am
For those interested, we have come up with a pretty good idea based off of a current requirement. We have a job that takes a nightly backup of the database and restores it to another database based on the day:
DatabaseA
DatabaseA_Mon
DatabaseA_Tue
DatabaseA_Wed
DatabaseA_Thu
DatabaseA_Fri
DatabaseA_Sat
DatabaseA_Sun
So, now it is as simple as writing a compare script and replacing data. Also, a complete database restore in production is as simple as a rename if they need to go back a day.
Still interested in thoughts on my previous post though.
Jared
CE - Microsoft
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply