Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nathan.russell
Starting Member
13 Posts |
 Posted - 2004-11-01 : 09:00:51
|
| Is there a way to setup a maintenance plan with a pre and post job ?By pre & post job I mean something external to EM and SQL that needs to be run either before or after the maintenance job.For example, I have a maintenance plan that backs up my databases at midnight each day.I have another maintenance plan that backs up the transaction logs every 6 hours every day.This is all well and good, but for it to be a 'complete' backup strategy, I want to get the backup files off the machine to somewhere else.My maintenance plans backup the db's and t/log's to the same 'root' folder (e:\mssql\backup), and they create a folder for each database. So, I have a nice directory structure for my backups. And they delete backups older than a given time - so the size of the backup folder is reasonably contained.What I'd like to do is after each t/log backup, run a batch file which zips the contents of e:\mssql\backup\.... and copies the resultant file to another machine; thus giving me a completely restorable system every 6 hours on another server elsewhere.But I can't find anywhere in the maintenance plan wizard to do this ?Or, is there a different approach ? Something like writing a batch file that uses sqlmaint to kick off the backup and then zip and copy afterward; and trigger this batch file with W2K task scheduler (not an idea I'm entirely happy with, but I can see it working)Any ideas ??CheersNathan |
|
|
MuadDBA
628 Posts |
Posted - 2004-11-01 : 10:17:30
|
| First, to get the obligatory plug out of the way....You should research how to do the things the maintenance plan does using nor mal jobs. You will learn a lot, you will gain much more control over the results, and you won't be relying on tools that can do some wierd stuff. For instance...did you know that if your daily backup completes a couple seconds earlier than it did the day before, it won't delete the prior day's backup, and you'll use twice as much space as you wanted to?Ok, now to answer your question:You can easily schedule jobs to co-ordinate with the maintenance plans. If you use Enterprise manager, go to Management, expand it, SQLServeragent, expand it, and click the Jobs folder. You will see jobs for each of your maintenance plans (sometimes two or three jobs per plan). You can edit those jobs (though EM will tell you that is not recommended) and add steps in front of or after the actions you wish to take place. |
 |
|
|
nathan.russell
Starting Member
13 Posts |
Posted - 2004-11-01 : 10:48:32
|
Hi crazyjoe,Thanks for your reply - as I read your reply, I found the exact same thing in one of my many SQL books !! Definately a case for RTFM !!I'm intrigued by your comment:quote:
You should research how to do the things the maintenance plan does using nor mal jobs.
Where can I look for this kind of information ? What is a 'normal job' ?quote:
did you know that if your daily backup completes a couple seconds earlier than it did the day before, it won't delete the prior day's backup, and you'll use twice as much space as you wanted to?
I thought this might be the case when I set this up - is there a better, more managable, way of deleting previous backup jobs ?CheersNathan |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-11-01 : 13:53:23
|
| Look at Tara Duggan's weblog's here on SQLTeam (see top navigation bar under Weblogs). She has a TON of scripts that do this using regular old SQL instead of some mysterious utility that hides what it is doign from you.A "normal" job to me is not one that calls the SQLMAINT.EXE utility, or, more accurately, is not created by the db maintenance plan wizards. If something is wrong with those jobs, it can be incredibly hard to troubleshoot. A "normal" job is one you create.Tara's scripts will back up all your databases and tlogs for you, as well as delete old copies of backups. |
|
|
|
|
|
|
|
|
Maintenance Plan 'Pre & Post' Jobs ?