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

When setting up database mirroring, it is possible to receieve the following error when starting the mirroring session between the Principal and Mirror databases:

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

Assuming the steps taken follow a pattern similar to below:

  1. BACKUP database DBMTest on SQLINS01
  2. BACKUP log DBMTest on SQLINS01
  3. Copy db and log backup files to SQLINS02
  4. RESTORE DBMTest with norecovery
  5. RESTORE log DBMTest with norecovery
  6. create endpoints on both SQLINS01 and SQLINS02
    CREATE ENDPOINT [Mirroring]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER)
  7. Enable mirror on MIRROR Server SQLINS02
    :connect SQLINS02
    ALTER DATABASE DBMTest
    SET Partner = N'TCP://SQLINS01.mydomain.com:5022';
  8. Enable mirror on PRINCIPAL server SQLINS01
    :connect SQLINS01
    ALTER DATABASE DBMTest
    SET Partner = N'TCP://SQLINS02.mydomain.com:5022';

The error would appear on step 8

Normally the reason for the error is because the backup file for the log or full backup has not been initialized and therefore contains more than one backup of the database on file.

Add the WITH INIT option to the backups to create a fresh initialized backup file

Advertisements

Progress of a Backup or Restore in SQL 2000?

In SQL 2005 onwards, you can use DMV’s to monitor percentage completed of certain operations (https://dbamohsin.wordpress.com/2011/02/08/dmvs-sys-dm_exec_requests-and-percent_complete/)

Is this possible in SQL 2000 apart from using the STATS flag on the BACKUP or RESTORE syntax…

Yes…

Use the following command against the SPID running the backup or Restore to see buffer information

DBCC OUTPUTBUFFER(spid)

Output Buffer                                                                
—————————————————————————–

00000000   04 00 00 5d 00 38 06 00 79 01 00 00 00 ab 44 00   …].8..y….«D.

00000010   8b 0c 00 00 01 00 14 00 33 00 30 00 20 00 70 00   ‹…….3.0. .p.

00000020   65 00 72 00 63 00 65 00 6e 00 74 00 20 00 72 00   e.r.c.e.n.t. .r.

00000030   65 00 73 00 74 00 6f 00 72 00 65 00 64 00 2e 00   e.s.t.o.r.e.d…