SQLServerCentral / Data Warehousing / Strategies and Ideas / How often do you back-up your data warehouse? / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 19:54:57 GMT20RE: How often do you back-up your data warehouse?http://www.sqlservercentral.com/Forums/Topic1418618-363-1.aspxThanks to everyone for the feedback!MarkThu, 14 Feb 2013 08:58:56 GMTMark EckeardRE: How often do you back-up your data warehouse?http://www.sqlservercentral.com/Forums/Topic1418618-363-1.aspxI 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.Thu, 14 Feb 2013 07:47:37 GMTSqlraiderRE: How often do you back-up your data warehouse?http://www.sqlservercentral.com/Forums/Topic1418618-363-1.aspxWe 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.Thu, 14 Feb 2013 06:58:25 GMTprescientdbaRE: How often do you back-up your data warehouse?http://www.sqlservercentral.com/Forums/Topic1418618-363-1.aspxIf 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.Wed, 13 Feb 2013 07:04:17 GMTRonKyleRE: How often do you back-up your data warehouse?http://www.sqlservercentral.com/Forums/Topic1418618-363-1.aspxAlso, 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.Mon, 11 Feb 2013 22:05:22 GMTshannonjkRE: How often do you back-up your data warehouse?http://www.sqlservercentral.com/Forums/Topic1418618-363-1.aspxHi 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.Mon, 11 Feb 2013 22:03:47 GMTshannonjkHow often do you back-up your data warehouse?http://www.sqlservercentral.com/Forums/Topic1418618-363-1.aspxHi 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,MarkMon, 11 Feb 2013 13:12:56 GMTMark Eckeard