I have a Microsoft database running in production of about 300gb, I need to transfer and set it up in a mirrored configuration on two different servers.
This has worked fine in my tests using smaller databases, but now I run into the following issues while trying to use the actual production database. It works about 1 out of 3 times, the rest of the times it ends in errors.
When trying to enable mirror on the primary it gives this error: "The remote copy of database "myDatabase" has not been rolled forward to a point in time that is encompassed in the local copy of the database log."
If I then try and take a new backup of the database log and restore on the mirror: "Msg 4305, Level 16, State 1, Line 1 The log in this backup set begins at LSN 64612000000218000001, which is too recent to apply to the database. An earlier log backup that includes LSN 64612000000217800001 can be restored. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally"
Here is what I am doing, it is also very time consuming and will require a lot of downtime, so if there is an easier way I would be very happy:
Server A is the current production server where a single instance of the db is running. Server B is the initial primary database server Server C is the initial mirror database server
I break the mirror from Server B and delete the databases on Server B and C.
ALTER DATABASE produktion SET PARTNER OFF
I take a backup from Server A and store it in a shared folder on Server B.
BACKUP DATABASE [myDatabase] TO DISK = N'\\Server B\share\myDatabase.bak' WITH NOFORMAT, INIT, NAME = N'myDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
I restore from the backup on Server B.
RESTORE DATABASE [myDatabase] FROM DISK = '\\Server B\share\myDatabase.bak' WITH MOVE 'mdflogicalfilename' TO 'D:\DEFAULT_2012_DATA\mdffile.mdf', MOVE 'extralogicalfilename_extra' TO 'D:\DEFAULT_2012_DATA\extralogicalfilename_extra.mdf', MOVE 'logfilename_log' TO 'E:\DEFAULT_2012_LOG\logfilename_log.ldf', NOUNLOAD, REPLACE, STATS =5;
I take a new backup on Server B (unsure if this can be avoided?)
ALTER DATABASE 'myDatabase' SET RECOVERY FULL; BACKUP DATABASE 'myDatabase' TO DISK = '\\Server C\share\myDatabase_full.bak' WITH INIT, FORMAT, STATS = 10; BACKUP LOG 'myDatabase' TO DISK = '\\Server C\share\myDatabase_log.bak' WITH INIT, FORMAT, STATS = 10;
I restore from the new backup and log on Server C with NORECOVERY
RESTORE DATABASE "myDatabase" FROM DISK = '\\Server C\share\myDatabase.bak' WITH MOVE 'mdflogicalfilename' TO 'D:\DEFAULT_2012_DATA\mdffile.mdf', MOVE 'extralogicalfilename_extra' TO 'D:\DEFAULT_2012_DATA\extralogicalfilename_extra.mdf', MOVE 'logfilename_log' TO 'E:\DEFAULT_2012_LOG\logfilename_log.ldf', NOUNLOAD, REPLACE, NORECOVERY; RESTORE LOG "myDatabase" FROM DISK = '\\Server C\share\myDatabase_log.bak' WITH NORECOVERY, STATS = 10;
Enable Mirroring on the initial mirror:
ALTER DATABASE myDatabase SET PARTNER = 'TCP://Server_B_FQN:myPort'
Enable Mirroring on the initial primary (this is where it fails):
ALTER DATABASE myDatabase SET PARTNER = 'TCP://Server_C_FQN:myPort'
The whole procedure takes about 7-8 hours, I have created each step as agent jobs to start the next one, but if something can be done faster it would help a lot.
It works about 1 out of 3 times, the other times I get the above errors from which I cannot seem to do anything but start over and hope it works.
Issue doesn't happen with small databases, so the issue is probably that the restore takes so long on Server C, that the DB on Server B some times creates new logs that are then out of synch, this would also explain that it works some times, but there are no changes comitted to the database doing the time.
Is there any way to restore all incremental logs and make sure the databases are in synch for mirror without restoring a full backup of the database?