Microsoft SQL Server Front End Blog

The writers of this blog are a part of the Microsoft SQL Server Manageability team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Agent, Data collector and other tools

Backing up a database in a DB Mirroring configuration

Backing up a database in a DB Mirroring configuration

  • Comments 1

Background:

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.

Solution:

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.

Sample Script:

(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

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

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)

BEGIN

       RAISERROR (N''Mirroring role is 2'',16,1)

 END',

              @database_name=N'master',

              @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup  a Mirrored Database',

              @step_id=2,

              @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'-- Script to backup database

BACKUP DATABASE [DemoDatabase]

TO  DISK = N''C:\Backup\DemoDatabase.bak'' ',

              @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_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

 

Additional References:

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 at
http://www.microsoft.com/info/cpyright.htm

Leave a Comment
  • Please add 7 and 1 and type the answer here:
  • Post
  • The title of this article itself is a bit confusing, because the fact is that you still cant backup a mirrored database.

Page 1 of 1 (1 items)