Creative Backup Strategies
-
We are having a problem with a rapidly growing db, limited disk space, and practically no hope of getting more disk space in the near future. Our tape library is inadequate (a new one is in approval, but could be more than 2 months from implementation). We are literally beginning to miss backups due to the lack of disk space/tape space. Currently, we are using full backups with t-log backups every hour. This is our corporate financial data (Peoplesoft), and this db typically grows about 1.5 GB daily. I'm looking at cutting back to differential backups, but have little (meaning no) experience with this type of backup. I would really appreciate any hints or pitfalls to look out for when using this strategy. Thanks.
Joshua Jones
Director, Global Database Services
PGi -
BOL 2000, explains differential backup. Search for "Differential Database Backups".
Maybe you should try and explain the severity of situation to the CEO again. Cost of losing data(disruption to business) versus disk / tape costs.
Maybe (Friday or Sunday) night full backup, rest of the days daily differential backup and lastly a hourly log backup.
In case of the a problem restore full and apply differentials upto the day and apply logs.
Make sure, every day, that the backups completed successfully.
Personally, shutdown and pretend the system crashed, the management will get quite interested of all staff start complaining.
-
Thanks; we've been seriously considering that "motivational technique".
I know that the scenario described is a standard one; I just want to find out if there's any blazing potholes I should be wary of. As you stated, this comes down to a matter of budget more than backup and recovery planning. Thanks.
Joshua Jones
Director, Global Database Services
PGi -
If you have a high number of transaction on the server the differential backup can in some situations take longer than a full backup especially later in the time frame. If it is all growth the best I can say is try a differential and see how long it takes.
However here are a few other things that might help.
1) Make sure you don't have a lot of watsed space in the Transaction Log and it is eating into your server space (full backup does not remove the transaction log).
2) Express to managemnet the longer they wait out the remianing space the more performace issues they will have. You will, if not already there, reach a point where the database is so fragmented in the index area it is making enormous hops around the data to accomplish the simplest task. The problem is you will be unable to correct with an Index rebuild when size hits bottom.
3) Also express that Order Bys an many other operations utilize the TempDB to accomplish sorting and suqeury related task. This means a lot of data will cause the TempDB to expand and potentially lock the server when it runs out of space.
4) Make sure there is no historical data that can be removed or at least archived so you can take control of your server.
5) (This one hurts to do sometimes and sometimes accomplishes nothing but always worth a try and saves face later when you can say you warned them) Let management know that your system is a key piece to the corporation and can mean disaster should it be lost. Let them know that you can support it as is but make no guarantees concerning it's ability to work properly without the proper hardware. Let them know you are warning them and that should the server stall or become unbearable slow you cannot do anything other than reboot and hope for the best. Beyond that they are the decision makers and they are responsible for your lack of being able to accomplish the job set forth.
Note: Be more tactfull in wording a I am just typing and thinking I would say something different with the same meaning. Also, keep a record of the conversation and the status of the server just in case a major meltdown occurrs and they start poiting the finger at you. In otherwords build a case against them before they cost you your job as I have seen this sort of thing happen.
-
I simply don't trust back-ups.
The only problem I have with differential back-ups is what happens if one of your backups corrupts?
OK, I know that 99 out of 100 times this doesn't happen but it only has to happen once to really ruin your day.
Let us suppose that your weekly full backup fails. How much use are your other 6 days of transaction log backups now?
-
Thanks Antares. I appreciate the notes. Hopefully, we'll get the SAN solution we are trying tp pitch.
Joshua Jones
Director, Global Database Services
PGi -
One more addition to Antares note( hope its not late to answer this question). Have a scheduled job to shrink tempdb on a daily basis. This should also help in saving space.
-
I think I've noticed a bit of confusion regarding x-log backups and differential backups. For a differential backup strategy, you can do a full backup weekly, then a differential backup each day (as someone above said, it may well take longer towards the end of the week - in that case, do a full backup twice a week?!). If you wish, you can also do x-log backups each hour or so....
However, when restoring, you only need the full backup, the LATEST differential (you do not need the differential from the previous day(s)) and then the x-logs that come after that latest diff backup.
If you are careful with your full backup (verify it after backup - there's an option to do this in maint plans that I use) and put it to tape, DVD, whatever (with verify) and keep a copy on HDD somewhere you should be quite safe.
But your overall issue, which others have pointed out, is to make management fully aware of the fire they're playing with to exonerate yourself in case disaster strikes and to hopefully resolve your problems! 🙂
Ian
-
If your DB has a lot of transactions its probably your Transaction Log it's growing and growing. We used to have one DB that had 7GB total and 5GB Just for Transaction Log before I put my hands on it. Again , Like Antares said check this issue and look in these discussion threads how to shrink/truncate Tlogs.
If you have historic tables wich have information that are not used (or problably not ) you could export the data to flat files and zip them ( saves a lot of space ). You can allways put back when needed using DTS.
-
Hi drjones23,
what I can't understand is you say that this data is your corporate financial data. This is a) quite sensitive data and b) mission critical data. In this case you should get everything you want and need to keep this data alive and running. Is your senior management that narrow-minded that it doesn't see the danger? I'm in a somekind similar situation for I maintain the server and the db's that keep our (my) asset management transactions. I just need to say I need more diskspace, more RAM and then we go and get it. Nobody really wants to lose that data, or even risk to lose some.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Anteras686 has nailed it right on the money. I too, have faced narrow-minded bottom line execs who think if they buy a used car it should run as long as a new one, if they hired their own mechanic to maintain it. They always seem to fail to budget for parts, growth, and disaster recovery. However, it is up to you and your boss to ensure that those things do get budgeted. On top of Anteras686's comments, I would also draw up a chart of how much space the system uses, and what will be needed in the future to budget yearly for just these scenarios. As for the problem at hand...
If anything, if you have a backup server in storage, even a spare workstation you can piece together and get a couple mirrored drives, you can archive off the older data to another server, and set it up for archival access for your employees. You can then free up your live server. You can schedule the data to be dumped off to the other server once a month or once a year. You can then back up the archive once a month to tape for off-site storage, and keep your existing backup routine. I firmly believe in full nightly backups when it comes to critical data. You can also set up to truncate the transaction log after it is backed up, on a nightly basis. Once this is accomplished, work toward improving on the redundancy and getting the system beefed up, or replaced with a larger system on the next budget.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply