July 19, 2010 at 8:21 am
Dammit! I even refreshed the thread before starting my reply, just in case Gail replied first!
At least our answers were consistent, I suppose :satisfied:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 19, 2010 at 8:51 am
July 19, 2010 at 1:10 pm
You didn't mention log backup verification, but that is a concern as well. I actually find that log shipping, in addition to being a healthy in place DR method, is great for transaction log verification.
We moved that way after we got burned by a corrupt log backup. My DBA partner unwisely ran an untested delete statement on live, deleted 50000 rows by mistake, and we had to get them back. We took the prior night's full and restored it to the side and started to apply logs. The delete happened around 10:30 AM, but when I hit the 6:45 AM log it simply would not restore. I had no errors in the backup process, VERIFYONLY, HEADERONLY and LABELONLY all worked fine on the log backup. It just wouldn't restore. We even called up Microsoft who said, "yes, it's corrupt, and no, there's nothing we can do to fix it". With log shipping, should such a condition happen, you would know immediately.
July 19, 2010 at 10:03 pm
Good point, Jeff; although places that do a point-in-time restore to test and/or development platforms from production backups would include log verification implicitly. I'm also a big fan of log shipping (or mirroring) for the purpose you describe.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 20, 2010 at 2:01 am
Ok Guys, one more question.
I have created a maintenance plan, i have creted a second maintenance plan (just a full back up WITH COPY ONLY) and i was wondering wether i should also include master database and / or all system databses, in my maintenance plan schemes... Stupid question i know, but could you please help me with this? So far the maintenance plan incudes for my production databases only.
July 20, 2010 at 2:12 am
Paul White NZ (7/19/2010)
although places that do a point-in-time restore to test and/or development platforms from production backups would include log verification implicitly.
I should hope so. I would venture to say that a damaged log backup is more serious than a damaged diff backup. A damaged diff doesn't break the log chain, a damaged log will.
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
July 20, 2010 at 2:17 am
Dfalir (7/20/2010)
i was wondering wether i should also include master database and / or all system databses, in my maintenance plan schemes...
Absolutely. If you don't back the system databases up, what will you do if they get damaged? Rebuild and lose all your logins, security, jobs, backup history?
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
July 20, 2010 at 2:22 am
Thank you. I was thinking the same thing, but i didn't include the system databases in the maintenance plan that has differential and transaction logs, as things dont change often in these databases (i think). So i created a second maintenance plan for full back up of these databases only once per day. I is my thinking right? is this "adequate"?
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply