6

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?

Darren
  • 203
  • 3
  • 7

3 Answers3

5

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.

Dale Wright
  • 91
  • 1
  • 3
2

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

CPU_BUSY
  • 2,322
  • 17
  • 17
1

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

Idriss
  • 125
  • 1
  • 7
  • Won't work. Maintenance plans don't have access to those variables. See this: http://serverfault.com/questions/47117/add-conditions-to-maintenance-plan-sequence-on-sql-server – squillman Jul 28 '09 at 18:55