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 123»»»

Automated backup plan Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 9:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:48 AM
Points: 326, Visits: 927
I've been reading about and configuring an automated backup strategy, and would appreciate a comment or two on my plans. First of all, the databases are small and not time-critical or mission-critical. Losing them altogether would be a disaster, but losing a day, or even several days worth of work would be no more than a pain in the butt.

It seems to me that using both differential backups and transaction log backups only makes sense for larger, sensitive databases. I have an enormous amount of storage space available, and a transaction log backup appears to use just about as much space as a differential backup. Using both seems to me would add an extra level of complexity to potential restore operations, for little benefit that I can see.

My thought is to take a full backup every night, zip and spool the zipped copies off to various remote backup devices (both on and off site) and take either transaction log or differential backups every hour through the workday. The full backups, zipped, are around 10MB, and I have an almost empty 3TB drive for primary backup storage. Offsite capacities are considerably larger.

Does this seem like a sensible plan? If so, would differential or transaction log backups during the day be better? I would guess differential, since you only need one good one for a restore operation, rather than having to chain a potential day's worth of transaction logs together, but I'd like to hear an opinion from someone who's had to perform such chores - I haven't had to (yet).

One thing does occur to me, that doing a routine like weekly full backup, daily differential, hourly transaction log would be good practice in a relatively small-scale environment, like I have now, rather than trying to deal with it all suddenly, if I do get something massive dropped on my head at some future date. Would it make sense to set up an unnecessarily complex backup routine just to get proficient at it? I'm not exactly short of projects, but this might be time well invested.
Post #1373879
Posted Wednesday, October 17, 2012 9:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 3,374, Visits: 7,296
I'm not an expert in this area (I'm just learning) but an advice I've read says that you shouldn't have a backup plan. You should have a recovery plan.
You need to be sure that the backups work and you can use them in case of a disaster.
This might add some work to your plan, but it'll give you something more trustful.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1373899
Posted Wednesday, October 17, 2012 10:23 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
You need to work out the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) for each of your databases.

Both of these will help you work out the appropriate recovery model and frequency/type of backups required.

... and as Luis mentioned above, you need to automate a restore process to ensure that all the lovely backups you have are actually worth the space they occupy!

Good luck and let me know of you need further help as I went through a similar exercise recently.


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1373935
Posted Wednesday, October 17, 2012 10:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:48 AM
Points: 326, Visits: 927
Yes, I've read that sort of advice as well, and it's certainly true, but it's difficult to restore something without first having backed it up. I was looking for some thoughts on the planned frequency and type of backups, given what I described about the databases. If the correct answer flowed automatically from an analysis of the situation, I wouldn't have posted my question at all.

I have no experience with restoring a hamburgered DB, just a few cases when I've destroyed something myself during testing, when all users were offline. In that case, I just deleted the database entirely, and did a complete restore from the full backup, to get back to pristine. Restoring something that users blow up, and maybe don't notice it for a while, is quite another matter. Experience with that provides insights that no amount of studying can replace.
Post #1373943
Posted Wednesday, October 17, 2012 10:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 8:43 AM
Points: 37, Visits: 82
I agree with everyone who's said you need to be concerned about recovery, but I also understand where you're coming from.

I'd say a weekly full (I like Sunday nights, myself, just for simplicity's sake), a daily, and an hourly would be good.

But I'd also run them in a test environment and make sure they can be restored completely without issue.


_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2
Post #1373945
Posted Wednesday, October 17, 2012 11:05 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:48 AM
Points: 326, Visits: 927
Jamie Scharbrough (10/17/2012)
I agree with everyone who's said you need to be concerned about recovery, but I also understand where you're coming from.

I'd say a weekly full (I like Sunday nights, myself, just for simplicity's sake), a daily, and an hourly would be good.

But I'd also run them in a test environment and make sure they can be restored completely without issue.

I agree as well - recovery is the ultimate goal, but once again, I can't even attempt a recovery if I haven't made a backup, and I can't make a regularly scheduled backup if I don't have some sort of plan. Testing restores is all well and good, but that comes AFTER having made a backup, to ensure that the backup was done correctly, and that it's useable.

Simplicity was exactly my thought - with a small database, does mixing full, differential and transaction backups make sense, or is it unnecessarily complicated? Obviously, a full backup once per minute would be the simplest from a recovery standpoint, but that's hardly practical.

You think, then, that such a cascaded full/differential/transaction approach makes sense, even given the situation I've described? If so, may I ask for your reasoning on the matter? It appears overly complex to me, but maybe I'm missing something, which is, after all, why I posted my original question.
Post #1373955
Posted Wednesday, October 17, 2012 11:11 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
pdanes (10/17/2012)
[quote]You think, then, that such a cascaded full/differential/transaction approach makes sense, even given the situation I've described? If so, may I ask for your reasoning on the matter? It appears overly complex to me, but maybe I'm missing something, which is, after all, why I posted my original question.


So let's take one of your DBs as an example. What's the most that your users can lose? And how much time can they wait for the DB to come back online?


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1373959
Posted Wednesday, October 17, 2012 11:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 8:43 AM
Points: 37, Visits: 82
pdanes (10/17/2012)
I agree as well - recovery is the ultimate goal, but once again, I can't even attempt a recovery if I haven't made a backup, and I can't make a regularly scheduled backup if I don't have some sort of plan. Testing restores is all well and good, but that comes AFTER having made a backup, to ensure that the backup was done correctly, and that it's useable.

Simplicity was exactly my thought - with a small database, does mixing full, differential and transaction backups make sense, or is it unnecessarily complicated? Obviously, a full backup once per minute would be the simplest from a recovery standpoint, but that's hardly practical.

You think, then, that such a cascaded full/differential/transaction approach makes sense, even given the situation I've described? If so, may I ask for your reasoning on the matter? It appears overly complex to me, but maybe I'm missing something, which is, after all, why I posted my original question.


Well, it also depends on how small we're talking and how comfortable you are with backups in general.

Personally, I'm currently running a small test server, and I do just as I've described, because I like knowing I can back up from any point.

This is honestly one where it really depends on you and your comfort zone. If you feel that a weekly full and a daily differential are sufficient, then go for it. I'd also try "breaking" it at some point too; if your backup is corrupted, if your backup just doesn't restore, whatever, so you can learn how to fix it too.

I guess my feeling is you said before that you wanted to practice. Doing the full monty here will help you with that, and for larger databases I know best practices recommend fulls/diffs/trans. So you might as well go whole hog to start with.

But this is just my opinion; feel free to take it with a grain a salt! :D


_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2
Post #1373964
Posted Wednesday, October 17, 2012 11:59 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:48 AM
Points: 326, Visits: 927
Abu Dina (10/17/2012)
pdanes (10/17/2012)
[quote]You think, then, that such a cascaded full/differential/transaction approach makes sense, even given the situation I've described? If so, may I ask for your reasoning on the matter? It appears overly complex to me, but maybe I'm missing something, which is, after all, why I posted my original question.


So let's take one of your DBs as an example. What's the most that your users can lose? And how much time can they wait for the DB to come back online?

There are currently six, the parameters for all are similar.

As I wrote in the original post, losing even several days worth of work is a bloody nuisance, but by no means a tragedy. That's certainly not my ideal, which is why I was thinking a full backup nightly, and either differential or transaction backups once per hour, and if I can somehow get a reliable "last changed" timestamp from the database, I will run the hourly only if someone has been using the database - there's no point in making repeated snapshots of something that isn't changing at all, which is often the case.

How long isn't a pressing issue either. Earlier this year, one of the drives in a server's RAID group went down just as several of us were leaving the country for a few days, and no replacement was immediately available. Rather than risk a real disaster, I simply shut the server down, told everyone that I would get on it as soon as I got back and we left. No sweat, people just worked on other things. I scored a drive from IT on my way back, plugged it in, waited a while for the RAID to rebuild and we were back in business. Again, several days is not my goal, but it's tolerable, as long as it doesn't happen often.

In practical terms, losing up to an hour's worth of work is fine, and if I can get it fixed by the next day, that's acceptable as well.

Does that help?
Post #1373980
Posted Wednesday, October 17, 2012 12:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 7,132, Visits: 6,293
Luis Cazares (10/17/2012)
I'm not an expert in this area (I'm just learning) but an advice I've read says that you shouldn't have a backup plan. You should have a recovery plan


So very very true.

pdanes (10/17/2012)
Yes, I've read that sort of advice as well, and it's certainly true, but it's difficult to restore something without first having backed it up.


Which kind of misses the point about the above advice (having a recovery plan). Recovery plans aren't about restoring the databases. Recovery plans are about "How much data can you afford to lose?"

Before you design your backups, you need to know the following:

1) How long can the database be down (and non-functional) while you are restoring?

2) How many minutes, hours, or days behind can your data be once you have restored? (I.E., how much data can you afford to lose?)

3) What kind of system (OLTP, OLAP, etc.) are you planning backups for?

4) How much your data changes over the course of a day, a week, and a month?

5) Is the database-read only or read-write?

6) How big is the database?

These are all very important questions to know the answer to before you decide what kind of backups you do, how often you do them, and how many backups you keep. If you don't know the answers to these questions, you're just guessing and guessing can get people into trouble.

Recovery strategies are very important. If you need to be able to do Point-in-Time restores, for instance, the database recovery mode is going to be set to FULL (or should be). If your database is in the Terabyte / Petabyte size range, you might be doing file / filegroup backups instead of full backups. Or you might be doing partial backups. If your database is read only, you don't need to back it up every day. Whereas if you're running a highly active transactional database where data gets updated on a minute to minute basis (think airplane companies or hotels), you might need differentials and complete / full backups along with transaction log backups.

Remember, a good strategy isn't about restoring a database. It's about optimizing your time and data protection. You don't need a backup to start with a recovery strategy. But if your databases aren't getting backed up at all, set up complete / full backups and transaction log backups with FULL recovery mode on until you do get your recovery strategy set. That's the safest bet to cover your bases.

Then sit down with your business users and establish a reasonable recovery plan SLA before Bad Things<tm> start to happen.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1373987
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse