Daily Database Backup Scripts
-
Comments posted to this topic are about the item Daily Database Backup Scripts
-
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 -
I thought you were going to post examples of your stored procedure but you only talked about it. Can you share the details please?
-
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.
-
Forum Newbie,
Links with downloads of the scripts should be available at the bottom of the article under the Resources section.
-
Thank you. Yes, there they are!
-
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/DBARantMinion Maintenance is FREE:
-
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.
-
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."
-
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/DBARantMinion Maintenance is FREE:
-
I did not find compression option. It would greatly save your space.
-
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/ -
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/DBARantMinion Maintenance is FREE:
-
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/ -
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/DBARantMinion Maintenance is FREE:
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply