January 13, 2020 at 3:38 pm
Hi,
We are in a situation to sync databases between two servers settings in a totally different domain . The app team have provided FTP location to copy the backups and the requirement is to sync the secondary every 15 minutes.
I tried creating copy only log backup every 15 minutes copied the backup to secondary and restore manually keeping the secondary in Standby Mode.
The Primary database have Regular Daily Full Backup, One hour Differential and 20 minutes log backup. The Copy Only restoration fails with error the LSN is recent. What might be going wrong? My understanding was that the COPY_ONLY is not affected by regular backups and the LSN break wont happen.
Can anyone help me achieve this task?
Automatic restoration every 15 minutes is another hurdle :-(.
January 13, 2020 at 3:55 pm
The option of copy_only on the log backup wont mark the log as re-usable.
So your 20 minute log backup regularly is marking the log as reusable to which your ftp is then complaining as the LSN sequence is broken.
You should only have one source of backup for your log.
Have you taken a look at https://www.brentozar.com/sp_AllNightLog/
January 13, 2020 at 6:22 pm
The option of copy_only on the log backup wont mark the log as re-usable.
So your 20 minute log backup regularly is marking the log as reusable to which your ftp is then complaining as the LSN sequence is broken.
You should only have one source of backup for your log.
Have you taken a look at https://www.brentozar.com/sp_AllNightLog/%5B/quote%5D
Thanks Anthony. Even if I use the regular backups, we have full backup every day so sync will be still a question,right?
January 15, 2020 at 4:51 am
Since copy_only was breaking LSN when run parallel with normal LOG backup I decided to use the same log backups and wrote a C# code to fetch the latest file from folder.
Was restoring only using log and skipped the differential and Full in between, Strange thing observed that a table created in primary was not created in secondary but all log restored successfully skipping the nightly full.
Can experts please help me understand why this happened?
January 15, 2020 at 12:22 pm
Yes once initialised by the first full/diff, then any subsequent fulls/diffs are not required, only the rolling log chain.
As for the table not being present, I take it you took the database into standby mode to check this? You positive that everything was committed on the principal database and exists on the principal. You took every log backup going and restore everything to a point in time "AFTER" the table was created on the principal?
Putting into standby will rollback/roll forward any changes as it needs to so if it was mid transaction when the log was backed up for the table create it would probably be rolled back as it hadn't completed at the time the backup was taken.
January 16, 2020 at 11:29 am
Yes once initialised by the first full/diff, then any subsequent fulls/diffs are not required, only the rolling log chain.
As for the table not being present, I take it you took the database into standby mode to check this? You positive that everything was committed on the principal database and exists on the principal. You took every log backup going and restore everything to a point in time "AFTER" the table was created on the principal?
Putting into standby will rollback/roll forward any changes as it needs to so if it was mid transaction when the log was backed up for the table create it would probably be rolled back as it hadn't completed at the time the backup was taken.
My mistake was the log was not restoring. We dropped the plan to use C# code and also introduced BizTalk to copy the files as the file copy was getting disconnected in between. Also the plan is to create a .csv file using SSIS to fetch the backup details from msdb database and insert the same in destination server new table . This will help check the latest backup and then restore the DB using the next file.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply