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?

2
  • +1, because you just parroted my question while I was stuck at site ;)
    – moobaa
    May 27, 2009 at 7:14
  • yes, yes I did ;)
    – Darren
    May 27, 2009 at 7:19

3 Answers 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

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.

1
  • yep, i use this too.. create the job on both principle & mirror & you're done Aug 7, 2009 at 9:02
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

1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .