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?
Sql-server – Maintenance plan for SQL Server Database Mirroring
clustermirrorsql server
Related Topic
- Sql-server – SQL Server 2005 Maintenance Plan Failure
- Sql-server – Unable to back up SQL Server databases using a maintenance plan
- Sql-server – Unable to back up SQL Server databases using a maintenance plan
- Sql-server – SQL Maintenance Plan – Send email on fail
- Sql-server – SQL Server 2012 Maintenance Plan succeeds but does not perform backup
- Sql-server – Permission issue with SQL Server backup via Mintenance Plan
Best Answer
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