Home » SQL Server » Configure SQL Server Database Mirroring

Configure SQL Server Database Mirroring


1) Take Full backup of database at Principle Server

BACKUP DATABASE USERDB1 TO DISK = ‘H:USERDB1.BAK’

2) Take Log Backup at Principle Server

BACKUP LOG USERDB1 TO DISK = ‘H:USERDB1_LOG.TRN’

3) Copy both backup files to Mirror server

4) Restore Full backup with No Recovery at Mirror Server

USE [master]

RESTORE DATABASE [USERDB1] FROM DISK = N’I:USERDB1.BAK’

WITH

MOVE N’UserDB1′ TO N’I:MSSQL12.SQL14I2MSSQLDATAUserDB1.mdf’,

MOVE N’UserDB1_log’ TO N’I:MSSQL12.SQL14I2MSSQLDATAUserDB1_log.ldf’,

NORECOVERY, NOUNLOAD, STATS = 5

5) Restore Log backup with No Recovery at Mirror Server

RESTORE LOG [USERDB1] FROM DISK = N’I:USERDB1_LOG.TRN’

WITH NORECOVERY, STATS = 5

6) Now, Configure Mirroring using SSMS. After connecting to the principal server instance, Right-click the database & go to properties. Select Mirroring from left side option.

7) Click Configure Security, Configure Database Mirroring Security Wizard welcome screen appears, click Next.

8) In the Include Witness Server dialog box, click Yes, and then click Next.

9) On the Principal Server Instance dialog box. Connect to Principle & Click Next.

10) On the Mirror Server Instance dialog box. Connect to Mirror & Click Next.

11) Enter Service accounts you wants to use & click Next.

12) Configuration complete, Click Finish to start configuration.

13) Configuration completed successfully.

14) Click on Start Mirroring.

15) Mirroring configured successfully & Click Ok

16) Sample Database Mirroring working fine.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Advertisements

2 Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: