October 13, 2010 at 5:10 am
Dear Gurus/Experts,
I have the following scenario:
*Maintenance Plan with 2 steps
*Step 1 does a full backup of a database to default directory and is configured for 3 databases (call them DB1, DB2 & DB3)
*Step 2 performs a CmdExec to run a BATCH file using the command line parameters of a compression utility to compress the BAK file, then delete the BAK file and then move the ZIP file to a seperate file server share.
The problem I have, is that Step 1 is done for DB1, then DB2 and then DB3 and then only does Step 2 run for the same databases.
This might be a 'newbie' question, but can I configure the Maintenance Plan, the steps and/or the associated Jobs to run Step 1 & then Step 2 for DB1 and THEN Step 1 & then Step 2 for DB2, etc.?
My reason for needing this functionality, is that the BAK files are created first and then the ZIP files and ONLY after all that are the ZIPs moved to the seperate file server share and thus a LOT of disk space is being used until the ZIPs start getting moved off.
Thanks in advance and kind regards!!!
October 13, 2010 at 7:50 am
Sounds doable. You've already go the steps in place for all 3 databases. Make additional steps and handle just 1 database in each step.
October 13, 2010 at 9:17 am
Why not create a seperate Maintenance Plan for each Db?
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
October 13, 2010 at 2:27 pm
chris.s.powell (10/13/2010)
Why not create a seperate Maintenance Plan for each Db?
Because then you don't have control over the sequencing of the backups. However, you could create separate maintenance plans and instead of setting up the schedule in SSMS you could create your own agent job and schedule each plan yourself.
Just be aware that the command line for a maintenance plan has to enable the sub-plan being called. Take a look at the normal command line for a maintenance plan and copy it.
The other option would be to have separate tasks for each database, and just set the dependencies for each task. In other words, start with a backup task for DB1 - connected to your compress and move task for that database - connected to the next backup task, etc...
And finally, you could use a single execute SQL task, build the command to backup each database - compress & move the backup file and a cursor to loop through all selected databases.
The last option could be done either with a maintenance plan - or with a straight SQL agent job.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 13, 2010 at 2:50 pm
Thanks to all for the suggestions; I should have been more clearer with my question.
Per Jeffrey, I am also trying to leverage off a Backup DB Step task in SSMS/SSIS which provides both sequencing AND the fact that simply ticking the required DBs using specified databases in that Backup DB Step to select the ones I need (perhaps considered a 'lazy' method).
The issue is that new databases could come on the scene at any time, and the idea is that ALL non-system/user databases are backed-up when we have proven the concept (i.e. A LOT! of space!).
Jeffrey, I will modify the maintenance plan to use a cursor as per your suggestion to specify and/or determine the databases I want backed-up and then script this fact... I believe this should work a-OK unless anyone picks up a problem with my extended logic! 🙂
Cheers again!
*EDIT* I believe the cursor solution could be considered the most dynamic/'elegant' of the lot (just an FYI as to why I have 'selected' that option... thanks again Jeffrey!).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply