The remote copy of database x has not been rolled forward to a point in time that is encompassed in the local copy

Question

Hi,

I set up DB mirror between a primary (SQL1)  and a mirror (SQL2); no witness.  I have a problem when I issue command:

alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go

The error message is:

The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account.  The domain account I login to do db mirror setup is a member of the local admin group.)

1. backup database DBmirrorTest on SQL1

2. backup database log

3. copy db and log backup files to SQL2

4. restore db with norecovery

5. restore log with norecovery

6. create endpoints on both SQL1 and SQL2

CREATE ENDPOINT [Mirroring]

STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = PARTNER)

7. enable mirror on mirror server SQL2

:connect SQL2

alter database DBmirrorTest

Set Partner = N'TCP://SQL1.mycom.com:5022';

go

8. Enable mirror on primary server SQL1

:connect SQL1

alter database DBmirrorTest

Set Partner = N'TCP://SQL2.mycom.com:5022';

go

This is where I got the error. 

The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy

 

Thanks for any help,

KT

Answer 1

Everything looks right in your procedure.

The only thing that I can think of is that maybe there is a job that is automatically backing up the log file on the principal?

Generally you have to backup one log file and restore it on the mirror before you can setup mirroring. Also, any log backup you do since the full backup must be restored on the mirror.

But basically the error message says it needs more log. Go ahead and backup the log again, restore it and try again.

Thanks,

Mark

Answer 2

Mark,

I got it working after several tries.

I used the same process I described.  I guess I made some mistakes in the earlier tries.

Thanks,

KT

Answer 3

Hello ,

 

I had the same problem in our Testlab and i had to repeat the backup - restore procedure 3 times before i got it working

this was just a test in our Lab with a small database but the next step will be the production database  ( 60 GB ) and i dont want to

back up and restore this database / Logfile several times before i finally got it working .

Does anyone know what causes this problem and what i can do to make sure that i have to backup / restore the database and  

logfile only once ?

 

Thanks in advance

 

Maarten Takens

 

Answer 4

FWIW, I've set up mirroring on my test and production servers and it worked merely with one Full and one trans log backup.  Went back to my test servers to set mirroring on my Sharepoint dbs and had to do:

1. One Full backup

2. One Translog backup

The DB went into "Restoring" Mode

3.  Did another Translog backup, and Restore, and got Mirroring to work.

 

Go figure.

 

 

Hth,

 

 

Chad

Answer 5

Hi Mark,

This is a really good suggestion and i have checked this work for me. 

Answer 6

RESTORE DATABASE @dbname FROM DISK = @backupfile WITH FILE = 1, NORECOVERY, NOUNLOAD;
RESTORE LOG @dbname FROM DISK = @backupfile WITH FILE = 2, NORECOVERY, NOUNLOAD;

These did the trick for me. I generated the commands through the "Script"-menu within the "Restore Database"-dialog of the Management Studio, because enabling mirroring was no problem when I restored through the "Restore Database"-dialog. My manually written SQL-commands were missing the "NOUNLOAD" part.