Re-establishing a mirror - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Re-establishing a mirror

Author

Topic

leoc50
Yak Posting Veteran

54 Posts

Posted - 2014-10-31 : 15:50:04

Hi All,
I've done mirroring several times and even in this current environment.
However, now the backups are in another set of servers and when the database is of considerably size ( >= 9GB ) it takes about an hour to copy from the principal to the mirror server (different cities).
A small database has no problem, but this 9Gb database keeps giving me SQL Error 1412
"The remote copy of database "DatabaseName" has not been rolled forward to a
point in time that is encompassed in the local copy of the database log.
(Microsoft SQL Server, Error: 1412)."

Searching thru Google I keep getting the same suggestions which I have followed to the letter.
I wonder if there is actually a step I'm missing from these:
1.Take latest principal database full and log backup with NO RECOVERY mode and copy the backups to the mirror server to restore it by the below order.
2.Restore the full backup with no recovery mode.
3.Then Restore the log backup with no recovery mode.
4.Then start your database mirroring session

Any help or idea would be greatly appreciate it giving the time it takes to copy the BAK files (using GUI or robocopy)

Thanks!
lc

- lec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-31 : 16:14:54

Are you applying ALL transaction log backups that have occurred since the full backup ran? Even the ones that ran on a schedule need to be applied. ALL of them.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

leoc50
Yak Posting Veteran

54 Posts

Posted - 2014-10-31 : 16:26:54

You might hit the nail right on, the job was disabled and I just checked the server... it is back online again ! :((

Thanks again Tara!!

- lec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-31 : 16:34:21

Be sure to enable it after mirroring is setup. I don't disable the job when setting up mirroring as I would forget to re-enable it which will lead to an outage eventually. Instead, I just manually restore them all.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources