November 27, 2008 at 9:37 am
Hi all
I have some maintenance plans on a principal server and on it's mirror which will back up all user DBs.
However....the mirrored maintenance plan always fails so we disable it so we don't keep getting alerts.
This is fine until one or more databases fail over to the mirror....then they don't get backed up....
So I was thinking about some automatic check in a SQL job, which would check the DBs on the server...if they are principal, then it checks to see if there is a backup job for this DB, if not, it creates it as a job and schedules it....is something like this possible?
I've already got some SQL jobs for various DBs on the principal and mirror which turn on and off automatically if the Db is in mirror/principal state by using categories for the SQL jobs based on the DB name...so the backup job for each DB would be in this same category and disable when mirror, enable when principal.
November 27, 2008 at 10:16 am
Perhaps change the jobs on both principal and mirror to check the status of the DB before backing it up. So
IF (select DATABASEPROPERTYEX('AdventureWorks', 'Status')) = 'Online'
Backup database AdventureWorks to disk ....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply