Database Mirroring in SQL Server is used to increase database availability. You can read more about database mirroring at http://msdn.microsoft.com/en-us/library/ms189852.aspx .
Database can be backed up only on the primary instance. On mirrored instance, database backup cannot be performed. Scheduled SQL Agent job backup job would fail if the job is run on instance that is not the primary.
Create SQL Agent job ( on both primary and mirrored instances) that backs up database after checking the database's state on current instance.
Agent job does the following
1) Job Step 1 – Check if the database‘s mirroring role on current instance is primary. Proceed to Step2 if condition is true; or abort the job with success
2) Job Step2 – perform database backup
Above mentioned agent job can be created on both primary and mirrored instance and run on a schedule. Even if database fails over, database backup is initiated by SQL Agent that is running on instance where database's state is primary.
(script assumes that c:\Backup folder is available on machines running primary and mirrored instance)
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Backup Mirrored DB',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId OUTPUT
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check if database on current instance is not primary',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=1,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DECLARE @mirroring_role int
SELECT @mirroring_role = mirroring_role
FROM sys.database_mirroring m, sys.databases d
WHERE m.database_id = d.database_id
AND d.name = ''DemoDatabase''
-- If mirroring role is 2 ; ie not principal
IF (@mirroring_role = 2)
RAISERROR (N''Mirroring role is 2'',16,1)
END',
@database_name=N'master',
@flags=0
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup a Mirrored Database',
@step_id=2,
@on_success_action=1,
@on_fail_action=2,
@command=N'-- Script to backup database
BACKUP DATABASE [DemoDatabase]
TO DISK = N''C:\Backup\DemoDatabase.bak'' ',
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
1) Database Mirroring http://msdn.microsoft.com/en-us/library/ms189852.aspx
2) Database mirroring using powershell script, refer to http://blogs.msdn.com/b/sethus/archive/2008/07/07/configure-database-mirroring-using-sql-2008-power-shell.aspx
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
The title of this article itself is a bit confusing, because the fact is that you still cant backup a mirrored database.