| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2006-03-03 : 05:58:47
|
| I'm sure this has been discussed before but I can't find it and it isn't in Kristens Sticky thread!I'm reviewing our backup strategy and have got myself confusedAll our backups are to file, which are then (hopefully) backed up by our network admins.We don't use the Simple recovery model as we need point in time recoveryWe do a Nightly Full Backup on weekdays. We do a Weekly Full Backup on a SaturdayWe do a Monthly Backup on a SundayEach of these backup files overwrites the preceding one. so for example the March one will overwrite the February oneWe do Transaction Log backups every 30 minutes during the working day.It's the transaction log backups that are confusing me at the moment. I don't want the backup file to get so big that it will fill up the server but I want to be able to restore to a point in time on a single given day.If I just append the backups to the backup file it is going to grow with no limit. If I use the option to remove the inactive part of the log then aren't I losing information? If I overwrite the backup file each time aren't I losing the committed transactions?What am I missing here?thankssteve-----------Oh, so they have internet on computers now! |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-03 : 06:24:54
|
| "What am I missing here?"You need ALL of the transaction backups since whichever full backup you are going to restore from.Kristen |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-03 : 06:33:33
|
| Steve,Are you doing this manually, or via a maintenance plan?Maintenance plans (iirc) will create a new file for each backup, with a different datatime. You apply these sequentially, as Kristen noted.If you are doing this manually, in a script, then can I suggest that you dynamically create the backup script - this gives you the ability to determine, and append, a datetime to the filename. I can do a quick sample if you want...CiaO*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-03-03 : 06:49:33
|
| Thanks guysI understand that I need all the transaction backups in order to do a restore. I suppose my question is do they all have to be in seperate files? Am I going to end up with about 30 backup files per day? Can they go into one backup file? Isn't this what happens if I don't use the option to remove the inactive part and do use the option to overwrite the existing backup?Wanderer I'm doing this manually. steve-----------Oh, so they have internet on computers now! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-03 : 06:51:55
|
| Pondering on this a bit more ...Backing up TLog more frequently gives you more, smaller, files.After many types of disaster you can make a TLog backup, and then use it as part of your restore:"Blast! I just deleted all the rows and my TLog back was an hour ago, I can't lose that work"Answer: Make a new, final, TLog backup, and then restore Full backup, all previous TLog backups PLUS the one you just made (but that one only to point-in-time)However, if your MDF/LDF files are partially eaten by aliens then you are only as good as your most recent TLog backups.That scenario is less likely than "Oops, the machine just fried" and now you are only as good as what you have got off-machine.To that end I would prefer a policy where the backups were copied somewhere else as-soon-as they were created (i.e. as part of the same task). So you get the most recent backup from Tape (or another server) and you get all the subsequent TLogs from the "other server".If you have a space problem then I would suggest:Set the backup folder to Compressed. (There is some contradictory evidence on this being a Bad Thing; my reckoning is that the file system supports it, these are write-only sequentially-written files, I can't see how it is possible for anything to go wrong - we've been doing it for at least 5 years; I will concede that it adds some complexity [at O/S level], and wastes CPU [all other things being equal]). That will save probably 50% of file space.Delete the historical TLogs as soon as the next Full backup has been made (or as soon after that as that Full Backup will have been copied to tape/somewhere else).Change the daily backups to use DIFFs rather than FULLs (now a restore is last Saturday's FULL, Last night's DIFF, all subsequent TLog backups) - that will save probably 80% of your daily (not weekly) backup file usage. Make sure that all REINDEX type stuff is done BEFORE the Weekly Full backup, not just after, otherwise you'll have DIFFs the same size as FULLs!We do DIFF "daily's" on our Web database boxes to get a balance of saving space, and achieving maximum restore history "on-line" - so that we aren't at the mercy of "Can you restore file X from tape please" as the response time for that sort of request is hours-going-on-days! I'm pretty sure you feel the same about relying on your IT folk to find a file for you, so you may be shooting at similar parameters.Possible downside is that the Maintenance Plan Wizard won't do DIFF backups for you, so you'll have to roll your own.Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-03 : 06:53:51
|
| " I suppose my question is do they all have to be in seperate files? Am I going to end up with about 30 backup files per day?"Preferable to be in separate files. If the 15th one is corrupted at least you can restore to that point, if the only file is corrupted you have diddly-squat.Note that the 48 [half hourly] TLog backups a day will be the same overall disk size as the one concatenated "big one"Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-03-03 : 07:20:12
|
quote:
If the 15th one is corrupted at least you can restore to that point
Fair point quote:
To that end I would prefer a policy where the backups were copied somewhere else as-soon-as they were created (i.e. as part of the same task). So you get the most recent backup from Tape (or another server) and you get all the subsequent TLogs from the "other server".
I agree completely with this but I never know when a backup has been done (or not!) - and that's unlikely to change. My only other server available is in the same physical cabinet, so not ideal.Many thankssteve-----------Oh, so they have internet on computers now! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-03 : 07:50:52
|
| "My only other server available is in the same physical cabinet, so not ideal"Would cover a hard disk system failure though, or a controller failure that scrambled the disks. The rest of the stuff is more important though!Kristen |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-03 : 08:05:50
|
| Steve,As Kristen pointed out, the multiple files is the way I would go. Going the differential backup route is a very valid method - I've used that at a previous client on their 180 GB DB.I've never used it, but maybe it might be worth your while to look at LiteSpeed (I think that is the products name?) - they apparently make fast, compressed backups... not sure if that's just full, or if it also does log backups.BTW - [quote=Kristen]"Delete the historical TLogs as soon as the next Full backup has been made (or as soon after that as that Full Backup will have been copied to tape/somewhere else)." [/quote]I would add onto that - after backup has been verified. Depending on you space issues, I might try and argue to keep those logs for 2 generations - i.e. Full backup(-1), log backups (x48 for every 24 hours), full backup (0), log backups.... delete log backups from before full backup -1. OF course, if you are overwriting your daily backups, then that's pointless, since it wouldn't be around. Just a thought - not sure how much of an issue space is there.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-03 : 08:14:49
|
quote:
Originally posted by elwoos
quote:
If the 15th one is corrupted at least you can restore to that point
Fair point quote:
To that end I would prefer a policy where the backups were copied somewhere else as-soon-as they were created (i.e. as part of the same task). So you get the most recent backup from Tape (or another server) and you get all the subsequent TLogs from the "other server".
I agree completely with this but I never know when a backup has been done (or not!) - and that's unlikely to change. My only other server available is in the same physical cabinet, so not ideal.Many thankssteve-----------Oh, so they have internet on computers now!
What level of changes can you make in the environment?Can you try add a step to the agent job to run a netsend on failure?if @rc<>0 xp_cmdshell 'net send steve Backup failed'just a thought....*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-03-03 : 08:23:39
|
there are a lot of ways to skin a cat...if your netadmin is doing daily system backup or ghost of the servers,of file backup via veritas (may the sql overlord help you)you can actualy overwrite the backup device  or if you really want to do it conservatively, daily full backup + 15 minute log backupwell this depends on how large is your database eh?--------------------keeping it simple... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-03 : 12:03:34
|
| We do daily full backups on all databases to disk. We delete the previous day’s backup file after the new backup is created.We do transaction log backups to disk 24x7 at least every 15 minutes. For some databases that have high activity, we do transaction log backups every 5 minutes to keep the transaction log file from growing too much.All database backup files and transaction log backup files are backed up to tape every day.We keep the transaction log backups for 3 days. The reason for this is so that we can do a point in time recovery from tape backups, and this insures that we can to it if we have no more than 2 consecutive failures in the daily tape backups.All of our databases and backups are on SAN storage, so if a server goes down, we can usually move the storage from a failed server to a standby server and get everything back on-online with no loss of data and minimum downtime.We do all our backups with maintenance plans, so all backup files are created with a date/time as part of the name. The one exception to this is on a server that is running Sharepoint. Because the Sharepoint admin can create a new site, and thus a new database at any time, we use a stored procedure to do backups on that server.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-03 : 13:54:40
|
I have not read this entire thread, so some of this may already be covered. Don't append backup file. It makes it harder to restore them. Perform a full backup daily so that if you ever have to perform a point in time restore, you can start with today's or yesterday's full backup and the tlog chain. If you are only performing a full backup weekly, then you'd need a ton of tlogs to get to say 6 days later when the problem occurs. The whole point to backups is to be able to recover. So make recovery as easy as possible on yourself.quote:
Originally posted by elwoosIf I use the option to remove the inactive part of the log then aren't I losing information?
This should not be used when using FULL recovery model unless you have run out of disk space, are not able to perform a transaction log backup, and the LDF file is full.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-04 : 01:14:09
|
| "I would add onto that - after backup has been verified"Indeed, my points at that part of my post were all supposed to be under the earlier heading of:If you have a space problem then I would suggestbut that certainly isn't obvious looking at my post again!Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-03-06 : 04:51:32
|
Thanks to all who replied, this has been very helpful and has certainly removed most (if not all) of my confusion. quote:
I've never used it, but maybe it might be worth your while to look at LiteSpeed
I have been considering this as our central network boys need it and I may be able to get a licence on the back of that. Our local supplier also sells Log Explorer which we bought from them. The sales guy got very emotional when I told him we didn't need LiteSpeed (at that time). I know there are some other similar products to this around including one from Red Gate which is much more reasonably priced but doesn't support SQL server 7. Does anyone use any of them? quote:
Can you try add a step to the agent job to run a netsend on failure?
I like this idea, not sure it will work in our environment but I will take a look. Thanks. quote:
if your netadmin is doing daily system backup or ghost of the servers,of file backup via veritas (may the sql overlord help you)
Jen you should know better than to ask this sort of question by now  My friends backup when ever they don't have more important things to do. They told me a while ago that they are backing up the contents of a single folder which is where I am putting my backups (and then crossing my fingers). I think I will also copy the backups to the second server which has a fair bit of room.Thanks again to allsteve-----------Oh, so they have internet on computers now! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-06 : 10:41:07
|
| "I like this idea, not sure it will work in our environment"Clearly the syntax needs to be:NETSEND NetworkAdmin "Oh-oh! Steve's Backup has failed ... Now you're in trouble!"Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-06 : 11:48:09
|
quote:
Originally posted by elwoos...Jen you should know better than to ask this sort of question by now My friends backup when ever they don't have more important things to do...
I had this same problem. The network admins would send me a report every week that showed all the tape backups that failed; it was always a fairly large spreadsheet. When I asked if they reran them, they said they would probably get them next time. Since they only ran them twice a week, that wasn't very comforting.  It took me a long time to convince people that daily tape backups were vital, and that it was important that they actually get done. I still like to double check things when I can, but the current crew actually seems to think it is important.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-06 : 11:52:04
|
| Fire drill. Nothing like it.Especially if you first backup the database to another server, test restore it, CHECKDB it, and then say "My DB is mucked up, luckily its the day after you do your backups so we're fine. Restore it as quickly as you can please"If that fails get your mate, a database recovery expert with a "no fix no fee" policy, to piece it together from the flux on the disk and send them a bill for gazillions. He can always buy you a Pint for old times sake of course.Repeat a week later ... until no longer necessary.Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-03-06 : 22:11:25
|
give them a real scare... i used to do this a lot (part of my "fire drill" and whenever i feel bored, don't know if the exercise sinked in though)unfortunately, i don't have a summary to refer to, but fortunately i can use this to my advantage, i'll request a restore on those dates that the file backup failed and make sure that the backup was already deleted (by a routine job, safe coz retention period is 4 weeks in our case)i'll make them feel that it's really really important (business interrupting request) that they do a restore or we'll get fined by the thousands, at the end of the day, it's a client requirement...that'll keep them going, then escalate to their manager, your manager, and enjoy the scene if they failed to recover, if you get caught, tell them it's a business continuity exercise that you're doing to test the setup... you'll come out being proactive instead of cunning  --------------------keeping it simple... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-06 : 23:14:56
|
| Those might work if someone actually cared if they could do a restore.I still fight battles like this:Me: "SQL Server X is down, I need it restarted."Admins: "Call the help desk, we'll get it in the morning."Me: "We need to run reports tonight. If you wait till morning, all our deliverables will be late."Admins: "We'll be in first thing in the morning."... and so on ...CODO ERGO SUM |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-03-07 : 01:19:44
|
| oh ok...so what you're saying people don't get bonuses and salary increases based on performance from where you're working eh?good for them --------------------keeping it simple... |
|
|
|
Next Page
|
Backup Strategies