Database Mirroring Issue

• Abi Chapagai

  SSCrazy

  Points: 2666

  More actions

  May 22, 2009 at 8:33 pm

  #205265

  Hello all,

  I have 3 instances of SQL Server Dev Edition on my PC. I was trying to set up Mirroring with Principle, Mirror and Witness instances. Principle Instance is named: SQL2005, Mirror: SQLMirror, Witness: SQLWitness. I was able to configure them but i could not start the mirroring. Here is the attachment of the screen shot that i got during the set up. Please help me out in this how can i set up database mirroring in my PC.

  Thanks for you help.

• thasteve

  SSCrazy

  Points: 2707

  More actions

  May 23, 2009 at 1:31 am

  #999282

  looks like you havent restored the latest log backup without recovery on your mirror database

• Suvendu

  SSC Enthusiast

  Points: 148

  More actions

  August 12, 2010 at 10:42 pm

  #1206389

  Run a full backup of your database.

  This can be done through the GUI or using a T-SQL command.

  --Run this on testServer1/principal server

  USE master

  GO

  BACKUP DATABASE TestMirror

  TO DISK = N'D:\DBBackup\TestMirror.bak'

  WITH NAME = N'Full Database Backup', INIT, STATS = 10

  GO

  BACKUP DATABASE TestMirror

  TO DISK =N’D:\DBBackup\TestMirror _Diff_1.bak' WITH DIFFERENTIAL

  GO

  BACKUP LOG TestMirror

  TO DISK = N'D:\DBBackup\TestMirror_Log.trn'

  WITH NAME = N'Transaction Log Backup', STATS = 10

  GO

  ----------Run a restore of this backup on your mirror.

  This can be done through the GUI or using a T-SQL command.

  The database restore must use the NO RECOVERY option, so the database stays in a loading state.

  Also the database name on the mirror must be the exact same name as the principal.

  --Run this on testServer2/mirror server

  RESTORE DATABASE TestMirror

  FROM DISK = N'D:\DBBackup\TestMirror.bak'

  WITH NORECOVERY

  GO

  RESTORE DATABASE TestMirror

  FROM DISK = N'D:\DBBackup\TestMirror _Diff_1.bak'

  WITH NORECOVERY

  GO

  RESTORE LOG TestMirror

  FROM DISK = N'D:\DBBackup\TestMirror_Log.trn'

  WITH NORECOVERY

  GO

  You will probably need to use the WITH MOVE option to specify the new drive and directory path for the data and log files.

  --You need to take sequential backup and restore the same as above. Any probl. please reply me...

  Thanks,

  Suvendu