September 12, 2011 at 11:43 am
I have some archive databases on the production server. For some reason we want to keep them there.
I have a maintence plan that backups all users databases on the server including the archive databases too.
We do full backup weekly, differenticial daily and transaction backup daily.
I know there is no point that we backup so much about archive database like other user databases.
But in all our maintenance plan on all servers we have a standard to backup all user databases which will include these archive databases, we do this, in case a new database is added, it won't miss it.
Now we have some disk space is running out issue.
One way I know is to backup archive only at weekend, no differienial or transaction is necessary.
But if we chose specifc databases, this will break our standard and I have to monitor and change the maintenance plan whenever a new database is added, or someone deleted a database.
I hope there is a way in maintence plan to exclude some databases, then I can create a new maintence plan just for those archive databases, but apprarently there is no this feature.
Any recommendation of how to implement this in a simple way?
Thanks
September 12, 2011 at 12:09 pm
While tightening up a client's instance I wanted the backup task to have this ability as well...and wondered why the option was omitted. I looked into it for a little while, including possibly modifying the Maintenance Plan XML in msdb directly, but came up empty on a way to do it.
When I am implementing a backup strategy from scratch I use my own DB backup routines written in T-SQL. To be fair my backup scripts do not support a "all user databases except these..." option, but it's something I may add.
Ola has a nice DatabaseBackup script which can replace the Maintenance Plan task you're using and since it's T-SQL you can modify it yourself to get the behavior you want.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 12, 2011 at 2:39 pm
My maintenance solution has support for excluding databases.
http://ola.hallengren.com/sql-server-backup.html
Ola Hallengren
September 12, 2011 at 4:17 pm
Well there you have it, from the man himself!
I have been looking at swapping out my backup process to start using your database backup process Ola, but as with anything I need to familiarize myself with it before I use it in production. For now I am still plodding along with my tried and true (and most importantly, familiar) scripts.
I recently got to a place with your IndexOptimize process to where I feel comfortable running and supporting it in production...and it is superb! I actually have an email I have been drafting to you over the last two weeks to talk about adding heap-management to the IndexOptimize process, but I have not finished writing all my thoughts on it. After tackling some client work involving heaps over the weekend I think I finally arrived at a coherent line of thought towards the topic and can hopefully finish that email to you this week.
Thanks for all you do,
Orlando
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 25, 2014 at 1:54 pm
Sorry for posting on such an old thread but I thought I would add to the thread for others to give another option...
You could also just add in a Maintenance CleanUp Task and specifically target your directory where the unwanted backups occur and just have it delete files based upon whatever "aged" time you want it to.
With that said, Ola's solution is a good one and appreciate his work on it and the sharing of the solution...
HTH
Dave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply