Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
+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

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer

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.

share|improve this answer
    
yep, i use this too.. create the job on both principle & mirror & you're done –  Nick Kavadias Aug 7 '09 at 9:02

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

share|improve this answer
    
Won't work. Maintenance plans don't have access to those variables. See this: serverfault.com/questions/47117/… –  squillman Jul 28 '09 at 18:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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