March 19, 2008 at 3:13 pm
Just when I had all the maintenance plan in place, and then realizing there were databases sitting on some instances that they just don't want backed up (this could be on prod, uat, or dev servers) or does not need to be backed up.
What do you guys do to "exclude" databases in the maintenance plan if all you have for a backup solution is a just maintenance plans?. I don't want to select or deselect specific databases to backup, otherwise, I might miss a database once a new one has been created.
I already created a script to run sqlmaint on each databases and I have a table containing the databases I want to exclude. I know this is one hell to maintain, but I know even netbackup has .bch script on each instance that has an exclude list.
(the company is still in the process of purchasing a backup software, but I reckon it will take like many months or year to make it happen)
_____________
Donn Policarpio
March 23, 2008 at 7:36 pm
The best way (for now since you will buy a third party backup application) is to script your backup procedure. Just exclude those databases that do not need backups. In this way, newly created databases will be included in the backup procedure.
"-=Still Learning=-"
Lester Policarpio
March 23, 2008 at 8:15 pm
Or you just administer your plan and specifically choose those databases that need to be backed up.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 23, 2008 at 9:35 pm
Thanks for the input guys. I hold it off making changes for now. There are just some handful of servers that seems to be not in our total control...and they just want the databases included in the backup regime. I just hate it when these kind of servers exist where it's free for all and creating databases doesn't even go to a proper change management. I have a job that sends me a report of dbs with no backup tho (that includes new databases)..so I guess I'll have to rely on it and administer it manually from the time being, not bad anyway to add a few more minutes to my daily chores.
_____________
Donn Policarpio
March 24, 2008 at 7:31 am
Donn, the beauty of a maintenance plan is that you can select all non-system databases. This will grab any database, new or not, and add them to the backup job. Makes for easy work if you are not the only one creating databases, they're always backed up. And Steve is correct, diffs are not part of a maintenance plan.
-- You can't be late until you show up.
March 24, 2008 at 11:11 am
I hear you. And excluding a database from a backup just simply blew that feature away.
_____________
Donn Policarpio
March 24, 2008 at 11:25 am
donpolix (3/24/2008)
I hear you. And excluding a database from a backup just simply blew that feature away.
Keep in mind that users "reserve the right to change their mind" at any old time about backing things up...usually after they've blown up the "useless" database they didn't think was important to back up, which is now a critical database...
My experience has also been that it doesn't much matter that the user told you they don't want it backed up - it still somehow reflects badly on you when they lose data as a result....
Might consider whether you just want to back it up "just because"....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 28, 2008 at 5:12 pm
I'm new to this internet Q&A stuff but a site I worked at used maint plans until I discovered backup ALL only did all databases that existed at time of plan creation!!!
So i knocked up a quick sproc (stored procedure) that backed up all databases to the backup server; and then >>>
a. SQL2005 threw wobbly at delayed writes. Actually worked but reported failures.
b. Oh! some databases don't need backing up all the time.
c. Unless things have changed or we didn't know enuff, maint plans don't truncate logs.
Being an application programmer and not a dba I used a sledgehammer:-
set up a server agent job with steps.
1. Run sproc - creates backup files, truncates log and create a 'copy' table for next step
2. Run activex vbscript - read copy table, copy .bak to bkup server, delete .bak., delete old .baks on target - keep 2 weeks worth.
3. Successful - output an OK text file for mailserver
4. Failure - output a not OK file for mailserver
The mailserver forwards the text file to some of us.
I've now seen other sprocs while perusing these forums - backup database (full & differential) and one I'd forgotten since my SQL6.5 days - shrink.
If I were you grab them all, and make 1 sproc.
I recollect writing sql somewhere to shrink based on the database size. Sorry can't remember where it is. Remember twas MB and not GB- may have changed since the old days.
April 1, 2008 at 7:35 am
DON'T shrink unless you have a very good reason to do so. This topic has been beaten to death on this site. Do a search and you'll find very compelling reasons why you don't want to do it and under what conditions you may want/need to do it.
-- You can't be late until you show up.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply