May 29, 2011 at 9:35 am
We are largely a M-F, 7am-7pm, shop. We do daily full database backups and hourly trans dumps. These dumps are stored locally on the sql server and backed up to tape in the night.
We are puzzled if we should continue backing-up the transaction log dumps to tape or just keep them locally (or on an another sql server at the same location)? (Over time we have stored thousands of trans log dumps and the full database backups that follow them. The value of those trans dumps is now limited.)
TIA,
Barkingdog
May 29, 2011 at 12:39 pm
First a thought on your current strategy in general:
If you store your hourly trans dumps on the same server, do you mean you're using the same physical drive(s) as used for the database itself (mdf and ldf files)? If so, what would you do if that specific drive fails and becomes totally unusable? In that case your log backups are pretty much useless...
You "only" need the transaction log files taken after the latest available and functional full or differential backup. And that's the biggest issue as well: you can't use a differential backup if the backup chain including the previous full backup can't be restored successfully. Therefore, you can't apply those latest tran log files either.
At the minimum level you need the log files taken after the last full or differential backup. Usually it's recommended to keep more - just in case...
We keep the last two full backups, all related differential backups and all transaction log files. All on a separate set of spindles. Full and differential backups are copied to tape.
A test if a full backup can be restored is mandatory.
May 29, 2011 at 1:10 pm
Lutz,
We keep the trans log dumps both locally on the sql server and copy them to another server (on the hour).
When you wrote: "At the minimum level you need the log files taken after the last full or differential backup". That would imply that the last step of our database backup job would delete all prior trans log files. That requires some VBScript fun. (We keep all of our trans log dumps in a separate folder. Our tape backup job doesn't know when the database was dumped and can't backup only those trans log dumps with a timestamp after the dump. It's not selective enough.)
Kind of kludgy.
Barkingdog
May 29, 2011 at 1:58 pm
I recommend keeping 2 fulls and the logs since around. After that, I've never seen the need to recover a database in the past to a point in time, so I wouldn't back them up to tape.
However, depending on how often to move things off site, perhaps there is some value if things were to go sideways today. You'd want yesterday's full offsite, along with as many logs as you could get.
May 29, 2011 at 3:24 pm
Steve Jones - SSC Editor (5/29/2011)
I recommend keeping 2 fulls and the logs since around. After that, I've never seen the need to recover a database in the past to a point in time, so I wouldn't back them up to tape.However, depending on how often to move things off site, perhaps there is some value if things were to go sideways today. You'd want yesterday's full offsite, along with as many logs as you could get.
I would expand on this - keep everything as long as you possible can. I cannot count how many times (on this site and others), where someone has run into an issue with corruption that wasn't noticed for xx days.
If you can only go back to yesterday - and that backup contains the corruption, you are going to lose data - guaranteed.
As long as you have an un-broken log chain - you can restore from a known good backup and apply the transaction log backups all the way to the current point in time.
I know for sure that several database systems were completely lost because of corruption that wasn't caught early enough. Especially where someone decided that running integrity checks was too system intensive and disabled the job.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 29, 2011 at 3:49 pm
You would normally only keep backups for longer than a few weeks if there was a legal or business reason to do so, e.g. there is often a legal requirement to keep financial data for 7 years, or you may wish to keep end of year data.
---------------------------------------------------------------------
May 29, 2011 at 10:39 pm
Jeffrey,
You wrote
>>>>
I would expand on this - keep everything as long as you possible can. I cannot count how many times (on this site and others), where someone has run into an issue with corruption that wasn't noticed for xx days.
>>>>
That really is troubling and makes me pause considerably to reflect on those trans log dumps.
(Of course, if the DB itself has been found corrupt I hope the trans log dumps themselves have not become corrupt too!)
Barkingdog
May 29, 2011 at 11:55 pm
Barkingdog (5/29/2011)
That really is troubling and makes me pause considerably to reflect on those trans log dumps.(Of course, if the DB itself has been found corrupt I hope the trans log dumps themselves have not become corrupt too!)
They should not be (there's a particular set of circumstances where corruption of the data file can get into the log, but it requires several things to happen in a specific order)
Of course, every full backup after the DB is corrupt will contain the corruption, the diffs may.
However, since you are running regular integrity checks (you are?) any corruption will be picked up very quickly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2011 at 2:18 am
1-Take Full Backup daily at night and then take file system backup on tape
2-Take Differential Backup at every 5 hours and then take file system backup on tape
3-Take Log backup as you mentioned hourly and then take second copy of this particular log backup on network drive or folder
On every Differential backup ,previous log backups will be useless then you can overwrite them
On every Full Backup , previous Differential and Log backups will be useless then you can overwrite them
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 30, 2011 at 2:24 am
Syed Jahanzaib Bin hassan (5/30/2011)
On every Differential backup ,previous log backups will be useless then you can overwrite themOn every Full Backup , previous Differential and Log backups will be useless then you can overwrite them
They're far from useless.
What happens if a user comes to you and tells you that he accidentally deleted some data 2 hours before you took that differential and can you please get the data back? Without the tran log backups the best you'll be able to do is retrieve the data as it was at the full backup, which may not be good enough.
What happens if the user only tells you a day later and you've deleted both diff and log backups?
That's ignoring the fact that you haven't mentioned testing the full and diff backups to ensure that they are restorable. Deleting the log backups between the full and the diff and then finding that the diff is damaged and not restorable is not a fun situation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2011 at 2:32 am
For the backup file curruption then you will use Verify the Backup on finished option and checksum option for the media test
you can't ignore the cost of the Tape and it depends on the policy of the organization ,how much data loss they can bear,in best practices tape backups always execute at night or when your database load is low not in production hours there must be performance penalty
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 30, 2011 at 5:54 am
Syed Jahanzaib Bin hassan (5/30/2011)
For the backup file curruption then you will use Verify the Backup on finished option and checksum option for the media test
Even that is not 100% safe. The only safe route is to actually do a test restore. I personally do checkdb on top of it just to be 100% safe.
I know it's not really doable on VLDB but we are fortunate enough to have that luxury here...
May 30, 2011 at 6:04 am
Syed Jahanzaib Bin hassan (5/30/2011)
you can't ignore the cost of the Tape and it depends on the policy of the organization ,how much data loss they can bear,in best practices tape backups always execute at night or when your database load is low not in production hours there must be performance penalty
Of course cost can't be ignored, but I'm not advocating storing 7 years of backups. Deleting the log (and diff) backup files the instant a further backup runs is foolish and just asking for trouble down the road. Backups corrupt and users mess up data. I've had to restore backups (full diff and log) from 3 weeks prior because some critical data had been accidentally deleted and was only noticed during month end.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2011 at 10:46 am
Barkingdog (5/29/2011)
Jeffrey,You wrote
>>>>
I would expand on this - keep everything as long as you possible can. I cannot count how many times (on this site and others), where someone has run into an issue with corruption that wasn't noticed for xx days.
>>>>
That really is troubling and makes me pause considerably to reflect on those trans log dumps.
(Of course, if the DB itself has been found corrupt I hope the trans log dumps themselves have not become corrupt too!)
Barkingdog
Gail has already answered about the transaction log backups, so I will just state that it is very important to keep as many backups online as you can afford (cost and space), and keep as many as you can afford on other media.
When (not if) - you get that request to restore some data from several weeks ago at a particular time of day, you want to be able to tell your customers that you can get that data. That is one reason...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 30, 2011 at 12:56 pm
Ultimately you do have to make some decisions on what to keep. Can your systems go back 2 weeks and still work? Or course, but if you go back that far, is there a difference between going back 14 days or going back 13.5 days (because you have log backups)? There may not be for many businesses, may be for others.
If t-logs are a significant tape cost, I might skip backing them up, but keep at least 2, preferably 3, full backups worth of logs on disk before you delete them and run regular restores and DBCCs on your databases to be sure you catch corruption as soon as you can. You can even automate those as tests.
IF disk space is an issue, there are third party tools that can help you check things, like Virtual Restore from Red Gate (Disclosure: I work for Red Gate). They can allow you to test a restore and run a DBCC without eating up disk space beyond the backup file.
You have to make the decision here on what to keep, and I think it's valid to consider not copying t-log backups to tape. Just make sure you make the proper decision for your company and don't make a blanket decision. You might decide one database is worth keeping all the logs, but not others.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply