Daily Database Backup Scripts

  • bernsteingreg

    SSC Enthusiast

    Points: 179

    Comments posted to this topic are about the item Daily Database Backup Scripts

  • back_of_the_fleet

    Say Hey Kid

    Points: 683

    I too have just completed developing a replacement backup process. The databases (around 45 of them across 2 servers, serviced via batch processing) are all BI related, and very occasionally a restore is required when we try to "undo" the impact of some bad data that has crept in. The old retention periods did us no favours (3 days) as we frequently wanted to go back to further - and this required requesting the return of an offsite tape which could take up to 48 hours.

    The new regime holds online 4 weekly full backups, and 15 days of differentials - all held on a network drive which is offsited via the Internet. I'll tune the retention policy as necessary. If we need to restore prior to the first differential we can roll forward be reprocessing the raw data (also backed up) from the OLTP.

    I mention the above because whilst your process is keeping the latest backup online, how long is it going to take to retrieve a tape if you need to go back more than the last backup on disk?


    John Rogerson
    BI Technical Lead
    Clear Channel International

  • miles-1005658

    SSC Journeyman

    Points: 86

    I thought you were going to post examples of your stored procedure but you only talked about it. Can you share the details please?

  • bernsteingreg

    SSC Enthusiast

    Points: 179

    SSC-Enthusiastic,

    I believe that is a good question. Retrieving the backups from virtual tape can take anywhere from 30 minutes to 2 days depending on the size. So in situations where our business needs dictate real time backup solutions (such as patient medical applications), we use different backup plans.

  • bernsteingreg

    SSC Enthusiast

    Points: 179

    Forum Newbie,

    Links with downloads of the scripts should be available at the bottom of the article under the Resources section.

  • miles-1005658

    SSC Journeyman

    Points: 86

    Thank you. Yes, there they are!

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    I'm sorry man, but this backup routine is only for the most basic of shops. If you've got a little departmental DB under someone's desk and you just need to make sure it's backed up at all, then you could get away with something like this. But it just doesn't take anything into account. I'm not sure that this routine does anything that built-in maint plans don't do. Of course, NEVER use maint plans, but that's not the point.

    I think this is maybe a first attempt at a real backup routine, and I encourage the author to keep at it and take some time to add some real features to it. Today's SQL environments are just way too complex to have such a limiting backup routine.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • gregwhite83

    SSC Veteran

    Points: 233

    thanks for sharing. This is a great start and keep up the good work 🙂

    I had a similar requirement but with a more complex environment as I didn't have enough storage to keep more than a days worth of backups before they were archived to tape.

    I use Ola Hallengrens scripts and made use of the Cleanuptime and the cleanupmode parameters.

  • Caruncles

    Hall of Fame

    Points: 3285

    Ours are attached. They've been modified recently to accommodate a log shipping setup. The logs cursor through a table which consists of database name and a bit field which can be set to off/on to allow for easy modification of which DBs get backed up or not. These are fired from agent jobs. The full backup runs nightly and the log runs hourly.

    We also do a nightly tape backup of database and log files as well as shared network folders, etc..

    These procedures have worked well for over 10 years.

    Constructive criticism is welcome!

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    Well, since we're all sharing backup routines, has anyone here tried Minion Backup?

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • SQL Guy 1

    SSCoach

    Points: 15732

    I did not find compression option. It would greatly save your space.

  • Michael L John

    One Orange Chip

    Points: 25946

    Good work. But.

    I find it hard to believe that people keep trying to reinvent the wheel.

    Minion backup, Ola Hallengren, https://ola.hallengren.com/ already do what you are trying to do. These have been tested, debugged, and are in production at some very large companies.

    Like Sean said, this is pretty limited to very specific needs.

    Do yourself a favor and look at Minion or Ola's scripts.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    I'm gonna go out on a limb here and say there's really no need to look at Ola anymore.

    Minion Backup does everything it can do and much more... and the features they have in common, Minion Backup actually does better.

    Sorry if that sounds conceited, but it's really just a fact.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • Michael L John

    One Orange Chip

    Points: 25946

    KenpoDBA (12/17/2015)


    I'm gonna go out on a limb here and say there's really no need to look at Ola anymore.

    Minion Backup does everything it can do and much more... and the features they have in common, Minion Backup actually does better.

    Sorry if that sounds conceited, but it's really just a fact.

    Well, the answer is "it depends". The tables behind Minion add flexibility, but on some servers maintaining them is problematic.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    Give me an example. Because in every single case I can think of, it's much easier to update a table value than it is to add a new job, or to change a param in a job step.

    In what case is it easier to change job settings than it is to update a simple table?

    You can even push out mass changes to the tables a lot more easily than you can make those changes on dozens of boxes through the job steps.

    And Minion Backup scales to be an entire enterprise system if you go the route of tying it into Minion Enterprise.

    But seriously, in what case is it easier to update job steps or create jobs than it is to simply update a table setting?

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

Viewing 15 posts - 1 through 15 (of 26 total)

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