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?

  • +1, because you just parroted my question while I was stuck at site ;) – moobaa May 27 '09 at 7:14
  • yes, yes I did ;) – Darren May 27 '09 at 7:19
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.

  • yep, i use this too.. create the job on both principle & mirror & you're done – Nick Kavadias Aug 7 '09 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.