Mirror Partner Agent Job To Auto Kick Back to Primary
** Script assumes all databases on your mirror partner should be in restoring state. If you have any online databases on the instance you use as the mirror, add them to the exclusion.
** Execute on mirror partner, not your production instance. Script executes in less than 1 second.
Let's be honest, mirroring blows. Those of us stuck on 2005 through 2008 R2 have to deal with budget constraints. When your primary kicks to the mirror or the mirror goes into suspended state, DBA's do not want to run to their computer during off hours and get them back in sync.
The script creates an agent job that runs every 10 minutes to check for databases on your mirror that are either in ONLINE or SUSPENDED state and takes corrective action.
USE [msdb]
GO
/****** Object: Job [Database Mirror Check] ******/BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Database Mirror Check',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Check to see if db on mirror is online, if online then attempt to set back to primary partner.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA', -- Update your operator here
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check DB State] ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check DB State',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=N'-- Flip ONLINE databases on the mirror back to the primary
DECLARE db_cursor CURSOR
FOR
SELECT name
FROM sys.databases
WHERE state_desc = ''ONLINE''
AND name NOT IN(''master'',''tempdb'',''model'',''msdb'',''distribution'');
OPEN db_cursor;
DECLARE @dbname varchar(255);
FETCH NEXT FROM db_cursor INTO @dbname;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC (''ALTER DATABASE ['' + @dbname + ''] SET PARTNER FAILOVER;'');
FETCH NEXT FROM db_cursor INTO @dbname;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO
-- RESUME databases in SUSPENDED state
DECLARE db_cursor CURSOR
FOR
SELECT db.name
FROM sys.databases db
JOIN sys.database_mirroring dm
ON db.database_id = dm.database_id
WHERE db.name NOT IN(''master'',''tempdb'',''model'',''msdb'',''distribution'')
AND dm.mirroring_state_desc = ''SUSPENDED'';
OPEN db_cursor;
DECLARE @dbname varchar(255);
FETCH NEXT FROM db_cursor INTO @dbname;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC (''ALTER DATABASE ['' + @dbname + ''] SET PARTNER RESUME;'');
FETCH NEXT FROM db_cursor INTO @dbname;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 10 Min',
@enabled=1,
@freq_type=8,
@freq_interval=63,
@freq_subday_type=4,
@freq_subday_interval=10,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20140828,
@active_end_date=99991231,
@active_start_time=223000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO 
