July 30, 2008 at 7:14 pm
Hello,
I have 3 servers whose databases are all interrelated and work in conjunction with one another. I therefore want to come up with a backup plan that includes all of them in the following sequence:
1.database_4 @ Server 2
2.database_3_1 @ Server 1 and database_3_2 @ Server 2 (This two are sync copies of each other)
3.database_2 @ Server 3
4.database_1 @ Server 3
The following conditions for backup are to be followed:
1.If database_4 backup succeeds or fails, proceed with the backup and to step 2. Issue notification on failure.
2.If either database_3_1or database_3_2 succeeds, proceed with the backup and to step 3, if both fail, abort backup. Issue notification if either or both database_3_1 and database_3_2 fail.
3.If either or both database_2 and database_1 fail, the first time, proceed with the backup and to step 4, the second time failure in succession, abort the backup. Any other condition, proceed with the backup and to step 4. Issue notification on point of failure.
4.Run backup integrity check to confirm integrity of the backup set, issue report on the backup status.
I will be running this procedure daily. All these servers are on public IPs, some located even more than 5000 miles apart. Kindly advice on how to create this procedure. The database servers are all SQL Server 2005. Thank you.
July 31, 2008 at 3:34 am
I would do this in a SSIS package instead of a procedure. First of all it's easier to have multiple connections and second you can easily define different branches depending on success or failure of a previous task.
[font="Verdana"]Markus Bohse[/font]
July 31, 2008 at 3:35 am
BTW if you're on SP2 or higher you can also do it in a maintenace plan, but thats basically a SSIS package as well.
[font="Verdana"]Markus Bohse[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply