February 11, 2013 at 1:12 pm
Hi All,
I'm trying to find a happy medium to a back-up plan for an up-and-coming data warehouse.
The DB won't be huge, as in TB's. Max size is unknown but I don't see anything above 100 gig for quite some time. I'll import the daily changes overnight since we work 7 days a week.
My SLA on data availability (via SSRS) has not been nailed down but I think we'll offer M-F 8-5 support with no more than 1 business day for a full restore.
With this said, could anyone offer suggestions on a back-up plan? I was thinking once a week but of course I'm thinking a restore would be needed 6 days in to the current cycle and I'd lose a lot of data and have to manually import that data.
Thanks,
Mark
February 11, 2013 at 10:03 pm
Hi Mark 🙂
If you are doing the typical once a night ETL refresh of data then a simple nightly backup work.
The Recovery Model I use is Simple as there is no transactional data that occurs or updates throughout the day. I simply take a full backup each night before the data load occurs, and IT Ops takes a nightly backup that they retain for 12 weeks so we have redundancy. If you don't have that type redundancy then you will need to decide your company's retention policy for backups, but that is the gist of it 🙂
I have been using this method for our Data warehouses for roughly 5 years with no hiccups, and it has saved a few disasters for sure.
Link to my blog http://notyelf.com/
February 11, 2013 at 10:05 pm
Also, the Data Warehouses I have been dealing with are roughly 150GB to 200GB for reference. They compress to about 15GB to 20GB. Depending on your physical server hosting this can also weigh in to your backup plan as well.
Link to my blog http://notyelf.com/
February 13, 2013 at 7:04 am
If you're only doing a nightly update, I agree with the recommendation to use a simple backup.
I have several updates throughout the day, however, so I make a full backup and take a transaction log backup when each of the updates during the day are complete. That way I can restore to the point to match the audit information that gets stored.
February 14, 2013 at 6:58 am
We do a nightly backup before the ETL load as well. Just for reference, we use Idera SQL Safe backup software for all SQL databases. It has superior compression (up to 90% compression) to the native SQL backup.
February 14, 2013 at 7:47 am
I use Simple and do a Full backup each night. I also import the daily changes into a seperate database in Simple mode and do a Full backup each night on it. You might consider this since you are not supporting during weekends and holidays, you can then restore back to the begging of the weekend and restore the Import & rerun the Update if needed.
February 14, 2013 at 8:58 am
Thanks to everyone for the feedback!
Mark
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply