SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How often do you back-up your data warehouse?


How often do you back-up your data warehouse?

Author
Message
Mark Eckeard
Mark Eckeard
Say Hey Kid
Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)

Group: General Forum Members
Points: 669 Visits: 505
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



shannonjk
shannonjk
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 840
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 Smile

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/
shannonjk
shannonjk
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 840
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/
RonKyle
RonKyle
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7148 Visits: 3623
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.



prescientdba
prescientdba
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 713
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.
Sqlraider
Sqlraider
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2727 Visits: 2315
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.
Mark Eckeard
Mark Eckeard
Say Hey Kid
Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)

Group: General Forum Members
Points: 669 Visits: 505
Thanks to everyone for the feedback!

Mark



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search