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 |
|
dewacorp.alliances
452 Posts |
 Posted - 2006-11-26 : 00:38:26
|
| Hi thereNeed an input for keeping how many days before gets deleted for sql backup file. All backup that we have is pointing to remote server (file servers) and we are using SQL native backup. Then every hour or so, the Veritas takes over these files and backup to tape and have a full backup each night. We have a big tape robot kind of thing and we don't have to change the tape. At the moment, in the SQL server, I setup only keeping 1 day only for both database and TLOG backup in the File Server before gets deleted. Which I guess it's Now raise my question that, as long as the tape backup is get backup everyday should be ok but what if it's not and failed ... let say on the weekend and no one see this tape backup till monday. For some reason, business need to restore from the one on the weekend. I am pretty much screwed. The tape backup is not my responsible but I can see this could happen potentially. How you setup this in the first place anyway? The things, if I setup as 3 days, there are an issue at our end regarding this space for this cause that means needs 3 times of capacity.Any thought guys regarding this? |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-26 : 03:07:19
|
| If you have disk space on your SQL Server keep the backups for longer - unless you have some confidential data that prevents it by increasing risk.How long would it take the Tape Boys to restore a file for you? We store a "recovery position" for two weeks because our databases are at an ISP and whilst they have phenomenal tape robots on a bad day they may take hours to get around to doing the restore that we need.We do Full backup once a week, DIFFs daily, TLog backups every 10 minutes or so. We keep full backup for 2 weeks, diffs for a week and TLogs for 3 days - so we can go back two weeks, but only to a Sunday (but we can get TLogs off tape if we need them of course). We figure that even on a bad day the ISP will get the TLog backups on to tape within 3 days, and more likely onto three separate tapes.In my experience recovery going back more than a day or so is only for comparison purposes ("What was this data a week ago, and why has it changed") rather than "I want to recover to 12 minute past 11am 13 days ago"Kristen |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-26 : 14:00:31
|
| I keep the Tlog backups at least 3 days on disk and the full backups on disk for one day. All are backed up to tape every day. That way, if a full backup is missed on the tape backup, I can recover forward from an older full backup on tape.CODO ERGO SUM |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-26 : 17:41:08
|
quote:
I keep the Tlog backups at least 3 days on disk and the full backups on disk for one day. All are backed up to tape every day. That way, if a full backup is missed on the tape backup, I can recover forward from an older full backup on tape.
Hi MichaelIn our case, I don't have a control on backing up tape ...cause it was done by someone else. But what you saying that I guess this is good solution (keeping 1 day full and 3 days Tlog) cause let say with the following schenarios:SQL backup:Friday - Full @ 1AM, TLog 2.15AM till - 12:00AM.Saturday - Full @ 1AM, TLog 2.15AM till - 12:00AM.Sunday - @ 1AM, TLog 2.15AM till - 12:00AM.Tape backup (veritas of the above backup):Friday: Full @ 1.15AMSaturday: FailedSunday: FailedOn Monday morning, let say the business needs something data back @ Saturday 8AM for some reason for comparison purpose.Obvisoualy, I don't have a full on Saturday cause it was deleted and no backup on tape as well. But I can always do restore Friday: Full @ 1AM and restore TLog for every single thing till 8AM on Saturday (cause Tlog still exist on disk).In summary, I need to restore:Friday Full @ 1AM, Friday, TLog 2.15AM - 12:00AM, Saturday TLog 2.15AM til 8AMIs in that right? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 04:27:58
|
| Why don't you run Tlog backups from 12.00AM to 2.15AM?I always thing those sort of backup schedules cause a headache downstream - e.g. someone introduces some Index Maintenance in that time period and Bang! the TLog files become huge because of it, with all the collateral damage that situation causes.Kristen |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-27 : 04:56:31
|
quote:
I always thing those sort of backup schedules cause a headache downstream - e.g. someone introduces some Index Maintenance in that time period and Bang! the TLog files become huge because of it, with all the collateral damage that situation causes.
Actually, that's only example. I just introduced to make it TLog backup as 24 hours operation. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 05:21:45
|
| "I just introduced to make it TLog backup as 24 hours operation"For 24/7 operation run the TLog backups on the same schedule for the whole 24/7. Don't stop running them for part of that time because people are not at work / the server is lightly loaded.Kristen |
|
|
|
|
|
|
|
|
Keeping Backup File in File Server