Maintenance plan for SQL Server Database Mirroring

Question

We're using SQL Server database mirroring and we're trying to create a db maintenance plan to backup the database. The problem is that when the server is not the primary database the plan fails. I was wondering what the best-practice is in this situation and is there a way to have the maintenance plan backup up only the primary database?

Answer 1

There is no hard and fast recommendation that i have found - One option Recreate the jobs and dependencies on the mirror server with the jobs disabled

Then use a WMI alert on the DATABASE_MIRRORING_STATE_CHANGE to enable / disable the job This job would also run on the primary and disable the job on failover - see this link for another variation

You could create a backup stored procedure with logic to only backup a database that is not in a restoring state like this

Answer 2

If you are using SQL 2008 the Backup Task has a lovely "Ignore Databases when the State is not online" option. This bypasses databases in a Restoring/Mirroring state for you.

For a SQL 2000 Box you can check the following property on each database

SELECT NAME FROM sysdatabases WHERE databasepropertyex(sysdatabases .Name, 'Status') = 'ONLINE'

For a SQL 2005 + Box you can use this

SELECT NAME FROM sys.databases systemdatabases  WHERE state_desc = 'ONLINE'

Then only backup databases that meet that criteria.

Answer 3

There should be a way, having a T-SQL Statement Task in the Maintenance Plan performing:

declare @primary bit = 0
select @primary=1
from sys.database_mirroring
where mirroring_role = 1

and using @primary as a condition to start the backup sequence. Please, let me know if it helped. Cheers