Simple Back Up Question

  • 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:

  • NOoooo! Your reply may state the same things, but with different info and from a different point of view. As usefull as Gail's for me! Thank you! Good thing you didn;t see Gails reply. 🙂


    "If you want to get to the top, prepare to kiss alot of bottom"

  • 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.

  • 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.

  • 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.


    "If you want to get to the top, prepare to kiss alot of bottom"

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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"?


    "If you want to get to the top, prepare to kiss alot of bottom"

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply