Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ras2a
Yak Posting Veteran
66 Posts |
 Posted - 2006-06-23 : 07:20:09
|
| We already back up our SQL DBs via our backup app (Veritas), however, I have set-up backups (data and tx logs) through the maintenance wizard (via EM). I noticed that it now saves database out seperately each time, so for the past 3 data backups we have 3 seperate files:BIDGroup_db_200606231000.BAKBIDGroup_db_200606231100.BAKBIDGroup_db_200606231200.BAKAm I going about this the wrong way, becuase if I leave this as it is, it will quickly consume HDD space?What is the preferred way to do this and is there way to sort of 'rotate' the backup so that it writes over anything over a certain age etc? Or do you simply have to manually delete old databases? If so, how do you then manage the backed up (.TRN) transaction logs?Edit: I just noticed that I am backing up this DB every hour. Should I change this to just once per day and rely on the tx logs being backed up at smaller intervals? Also, I've just noticed (in EM) that you can set to 'remove files older than' etc. How long should I be keeping old DBs/Logs for? What is considered the norm?thanks a lot dudesras |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-23 : 09:06:50
|
| That's probably a good thing.You need to set it to delete old files.Have a look athttp://www.nigelrivett.net/SQLAdmin/BackupAllDatabases.htmlWhich will do full, diff and log backups and delete old files.Note that you shouldn't backup the log from two places as you will need them all for a restore.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-23 : 10:49:47
|
| Cheers nrAlso, what sort of interval should I have between log backups? Does 15-30 mins sound ok? I suppose it depends on how critical your data is?Finally, if setting up a simple backup (not using the stored procedure you provided), what sort of interval would you recommend for full backups? Every couple of hours?Regardless, I think I will end up using that procedure - it sounds more comprehensive.Thanks againras |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-06-23 : 16:59:20
|
| When in the maintenance plan, look at all the tabs (there aren't many) and read all the options on each tab. It's very friendly and flexible, and fairly self explanatory with the help of google.You're right, it differs from environment to environment, but it's common to...Have one complete BAK per day (maybe in the middle of the night when no one is working)In the Maintenance plan, there is the option to specify the number of BAKS to have at any one time. You do not have to delete them manually. ie. if you specify 3, then when the backup is performed, SQL Server checks the number of baks and if there is more than 3, deletes the oldest one. This way your disk is not rapidly consumed.The same applies to the TRN t_logs. There is an option for this too.Depending on the system, I keep Complete BAKs of the user databases for 3 days. More so not because a user may want data from 3 days back, but because it is not uncommon to find one of your baks are corrupt, so you may have to restore from the bak before.Tran logs are kept for the same time as the BAKS, because you need the set of TRNs that correspond with their BAKS.The interval between t_log backups really does differ between systems. We have a very important finance system, with lots of updates. This is a very busy system. I measure how often I should run the t_log backups by the level of potential bollocking I would get from my management if the users lost lots of work! If I needed to restore the system, it is likely that they would need to revert to as up-to-date data as possible. So I backup every 15 minutes. SQL Server can handle that.But the majority of my other servers, I only need to backup t-logs, say, every 4 hours, because it wouldn't be such a big deal if data up to four hours was deleted.It does take longer to restore a database, the more tran logs you have though. So, are you more likely to get bollocked because users are waiting half an hour for you to restore the database or because you could only restore to an hour ago?You will always feel uncertain with what your doing until you practise backing up and then restoring. I still do that. Every month I restore random databases to test servers for peace of mind. |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-23 : 19:51:31
|
| Drewsalem, many thanks for that matey. Appreciate your helpras |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-24 : 02:11:19
|
| Drewsalem, nice concise answer."We already back up our SQL DBs via our backup app (Veritas)"Don't! Lots of people post questions here when they have problems with 3rd party software which backs up direct from SQL to tape. Often when they try to restore!Get SQL Server to backup to disk.Get Veritas to backup those backup files to tape.Make sure that the main daily FULL backup is created shortly BEFORE the Veritas backup runs."to get bollocked because users are waiting half an hour for you to restore"Hehehehe ... you could consider also doing a Differential SQL backup in-between Full backups to reduce the restore time, if you have to do TLog backups very frequently. But Differential backups are not included in the Maintenance Wizard stuff, so you'd have to do those using some SQL code you would write.Kristen |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-24 : 12:06:32
|
| Kristen, so you would really advise against using 3rd party tools to backup SQL DBs? No probs, I will disable SQL backups via Veritas and simply use the disk backups that I have enabled via the maintenance wizard. I have them backing up to a seperate disk. Will configure it so that there is 'one' FULL backup (.BAK) each night BEFORE Veritas starts it's backup (thereby backing up the db/logs to tape)Is just the one full backup (.BAK) per evening, sufficient? I have transaction logs backing up every 15 mins.cheers Kristenras |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-24 : 14:48:39
|
| "Is just the one full backup (.BAK) per evening, sufficient? "I would think so - unless your circumstances are significantly different to the "norm" amongst companies. Very high numbers of Inserts/Updates might cause me to suggest otherwise, but that would be pretty rare.You DO need to configure your Tape Backup so that it backs up ALL your SQL backup files - don't rely on having a copy on the local disk, and maybe also on another machine. Its only backing up the database directly to tape (using 3rd party software) that I would not recommend.I'm assuming your IT people have given thought to getting tapes off-site, disaster recovery if the building burnt down, and all that sort of thing?"I have transaction logs backing up every 15 mins"You might like to give some thought to the fact that that TLOgs every 15 minutes is nearly 100 TLog backups a day - which (assuming an emergency recovery on average half-way between full backups) will average 50 TLog restores. That's quite a pain to set up if you haven't done it before, so I recommend a Fire Drill and then write Operating Procedures (including scripts, or "how to use Enterprise Manager to restore TLog backups" notes) so that when the bad thing happens you can get the users up & running as quickly as is possible.As I (or someone else in these threads) mentioned earlier on: if your total daily TLog backup is a significant size the recovery time will be significant too, and you might want to consider a Differential backup (mid way between Full backups, for example) to shorten thenrecoverytime, but Enterprise Manager Backup Wizard will not do this for you, so your will need to be brew-your-own.For your Fire Drill best to do a practice on 90% of the way between Full Backups, rather than half way - i.e. do the worse-case-scenario.And make 100% sure that you have safe copies of your database in case of corrupted backups etc. For example, chuck all the users off, disconnect them so they cannot update anything, take a full backup, restore it onto ANOTHER machine, do a DBCC CHECKDB on that machine to make sure the restore was OK, THEN, and only then!, start your Fire Drill.Kristen |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-24 : 15:27:26
|
| Kristen,I am actually our IT department (lol) we are a small company with 30 users. I, of course, take our latest (AIT) tape backups offsite each night (the rest are stored in a fire-rated safe onsite) In addition, we have a full disaster recovery solution in place. I most certainly would never rely on backups to the HDD alone - Our backups jobs backup backup everything on our server. I currently have the SQL backups pointed to a completely seperate drive for fault tolerance. I am also thinking of backing the SQL DB up to another location also (so it's backing up to 2 seperate drives?) Also, this next week, I am moving our SQL Server to a completely new server (HP DL380 with RAID1/5 array).As for the tx logs, the backup regime I have is as follows: Mon - Fri (9am > 5pm). None of our staff are in after 5pm (or at weekends), therefore, no other transactions (I assume?) take place. So,having tx logs in place every 15 mins equates to around 32 .TRN files for the 8hr day. Furthermore, our database is currently really quite tiny (around 260MB) and most of the tx log backups are between 200k+ > 2MB (or thereabouts)...so with the correct backup 'rotation', disk space will not be an issue - Well, not for a good while.I will deffinitely look into the 'differential' backup - nr posted this lingk (further up the thread):http://www.nigelrivett.net/SQLAdmin/BackupAllDatabases.htmlI haven't had a look at it yet, but hopefully I can adopt that for our purposes. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-24 : 17:14:41
|
| "I will deffinitely look into the 'differential' backup"Doesn't sound like you need it (and it will only add complexity, which in turn might cause something to fail ...)"... take our latest (AIT) tape backups offsite each night"You might like to consider taking the previous day's tape off-site. I don't know the odds, but I think its more likely that someone will want to recovery from the most recent (i.e. yesterday's) tape, which is now off-site!, rather than the loss of 2 days data being critical when the building is totally destroyed.Don't bring the off-site tape into the building until the next off-site tape has been taken to the off-site location.Kristen |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-24 : 19:17:38
|
| Kristen,I don't think I made myself clear in my last post - This is how I 'currently' move tapes offsite:Example: Put Monday's tape in on Monday (just before leaving work for the day), taking Friday's out and taking that home with me for the evening. The cycle continues with me Putting Tuesday's tapes in on Tuesday (again, just before I go home), now taking Monday's tape home with me....and so on, throughout the week. I assume this is the general procedure for off-site storage. All other tapes are retained on-site in a fire-proof safe. We were also thinking of looking into SAN solution's, but this is not something my company feel's we need (along with many other things - lol). Hell, I'm still arguing for a 'proper' air-con system in our server room - hah!ras |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-24 : 20:35:14
|
| I recommend retaining tape backups longer than one week. If important data is deleted or corrupted, you may not notice it for a while.I have a rotation of daily tape backups that are saved for 8 weeks, and a weekly tape backup that is saved for two years.You should also look at some kind of off-site storage facility. Most fire safes are not rated to protect electronic media. They are only designed to keep paper below the ignition point. Tapes will be made unusable by the high temperature and humidity even in a fire safe. Tapes should be stored in a safe rated for electronic media, but even a media rated safe can be destroyed in a fire.Obviously the cost is more, but you have to evaluate that against the cost of lost data and potential liability.Just remember that no DBA ever got fired for having too many backups.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-25 : 00:29:43
|
Just playing devil's advocate here to double-check your process:OK, so on Monday at 5PM you take Fridays tape out of the drive, stick it in your bag, and put Monday's tape in ready for Monday night's backup.You leave the office and Friday night's backup is now off-site; there is no "current" tape (on-site) to restore from until the Monday night tape backup has finished (that may be fine, but lets assume you are sick that night and someone else has to do the restore - how hard is it for them to get the tape, and is that OK?)Then on Tuesday morning (you're feeling fine again!!) you come in. Have you got Friday night's tape with you? - because if so then there is no tape off site - so you should actually have Thursday's tape with you, and leave Friday's tape off-site - that one will come in tomorrow.So, assuming you have Friday night's tape at home still then you have Thursday night's tape on site, and also the tape that was just made on Monday night.Tuesday morning the likely recovery scenarios are:1) Total server failure - Monday night's tape is good.2) Accidental deletion on previous day (i.e. Monday) that someone has just noticed, you now need Friday night's tape - which is off-site.So for me I would want to take the second-to-last tape off-site, not the most-recent (i.e. on Monday take Thursday's tape off-site, and Friday's tape goes in the safe).In a total-building-failure scenario I consider that a two-day data loss is acceptable (if it isn't then you will have warm-standby's and all sorts of five 9s policies etc.  ), but a quickie-recover of a deleted file from yesterday doesn't need the delay of going to get the off-site tape IMHOAlso important is that the off-site location is sufficiently far away, in geographical terms. A chemical spill could cause evacuation of a fairly large area, so a building only a block away is probably not enough.Reading that through its clear as mud - but hopefully your strategy is even more robust than that.Kristen |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-25 : 13:33:08
|
We employ 4 weeks worth of backups ! 20 tapes in all. Using 2 Sony AIT 2 drives (in seperate servers). Our offsite backups are retained in a secure (yet accessible by relevant personnel) location. This is not in close proximity to our main head office, nor is it sufficiently far enough away to be a problem collecting the tapes should I be on hols or ill etc.Cheers for all the replies, must say that you guy's are very helpful indeed. It is much appreciated - cheers dudes  |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-06-27 : 08:18:05
|
| I have this IT guy that keeps using ARC serve SQL Agent to do backups.I tell him not to and he keeps activating it. His reasoning is it is part of the package of "Full System Recovery" that ARC Server sells.I worked around it by not letting him use ACR serve SQL Agent jobs that do Tlog backups so my log shipped backups stay in sequence.What burns me about this guys logic is he has never tried an ARC Serve full system recovery. I know the one time I tried it, it failed,and that was without SQL."it's definitely useless and maybe harmful". |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-27 : 09:56:23
|
quote:
Originally posted by Sitka
I have this IT guy that keeps using ARC serve SQL Agent to do backups.I tell him not to and he keeps activating it. His reasoning is it is part of the package of "Full System Recovery" that ARC Server sells.I worked around it by not letting him use ACR serve SQL Agent jobs that do Tlog backups so my log shipped backups stay in sequence.What burns me about this guys logic is he has never tried an ARC Serve full system recovery. I know the one time I tried it, it failed,and that was without SQL."it's definitely useless and maybe harmful".
After a while, it isn't worth arguing about any more, and you just have to get a stick and whack heads.You might want to consider taking away access to SQL Servr so he can't do that. CODO ERGO SUM |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-06-27 : 10:34:04
|
Funny thing, after my rant, I go and check, and he turned it off again.It wouldn't be so bad if he would put some effort into a bit of learning. But after trying to explain a few thousand times it just gets to painful if the other person won't practice or read. We all have our priorities and focus but to see no increase in understanding after years is pretty disheartening. The recent reboots to the server when an LTO2 tape got shoved into an LTO1 drive, (although an honest mistake) still linger.quote:
You might want to consider taking away access to SQL Servr so he can't do that.
It's his stuff ultimately, I'm outranked and to the rest of the organization it's all just "computer stuff". He actually told me once it's good for stuff to break, or to remain difficult as it creates job security. Stangest perspective I've ever heard."it's definitely useless and maybe harmful". |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-29 : 05:51:13
|
| I know what you're saying about this. I have a similar situation at my place of work whereby people simply will not listen to reason - get's on your tits after a while. Regarding ArcServe, we used to use that back when we were using Novell - It was crap. With our current system (Veritas), I 'have' had to do a real disaster recovery and everything went fine - was impressed. Although it's always best to do test drills in the labs :DAgain, I must say that you guy's are both very knowledgeable and very gracious with your help - It is very much appreciated.cheers guysras |
|
|
|
|
|
|
|
|
Backing up databases and logs via the wizard