Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How often do you back-up your data warehouse? Expand / Collapse
Author
Message
Posted Monday, February 11, 2013 1:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 9, 2014 11:34 AM
Points: 128, Visits: 489
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



Post #1418618
Posted Monday, February 11, 2013 10:03 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:36 PM
Points: 586, Visits: 822
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/
Post #1418743
Posted Monday, February 11, 2013 10:05 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:36 PM
Points: 586, Visits: 822
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/
Post #1418744
Posted Wednesday, February 13, 2013 7:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:49 PM
Points: 804, Visits: 1,989
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.



Post #1419494
Posted Thursday, February 14, 2013 6:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:51 PM
Points: 27, Visits: 661
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.
Post #1420036
Posted Thursday, February 14, 2013 7:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 1,296, Visits: 1,814
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.
Post #1420074
Posted Thursday, February 14, 2013 8:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 9, 2014 11:34 AM
Points: 128, Visits: 489
Thanks to everyone for the feedback!

Mark



Post #1420136
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse