1

Problem Statement - I want to configure DR site in SQL Server 2012 with the existing backups.

The backup scenario is as below. FULL – on every Sunday, DIFF – daily except Sunday, LOG – every 15 minutes

Now, I want DR site for my production for which I am interested in using Log Shipping. But, the problem in Log Shipping is that it will initially restore full backup then regularly ships the transaction logs to the secondary server.

But, as in my present scenario if I use this then after every FULL or DIFF backup, my Log Shipping will break as the LSN will get change with respect to my current backup at secondary server.

So, is there any way to configure Log Shipping with using existing backups or do any one have any custom procs or scripts which will help us out?

  • if I use this then after every FULL or DIFF backup, my Log Shipping will break as the LSN will get change with respect to my current backup at secondary server. This is not true. A full or diff wont ever break your log chain. A log chain is broken if you take an adhoc NON COPY ONLY log backup. – Kin Shah Aug 11 '16 at 13:48
  • Thanks Kin for the response. I am going to implement this in current week. If any issues faced, will post here again :) – palmon Aug 15 '16 at 7:56
1

I don't understand your issue. You can definitely log ship while performing Full and Diff backups. Are you trying to have some manually performed version of log shipping? Performing a Full or Diff backup will cause no issues, log shipping just uses log backups so the chain will not be broken.

Have you actually tried this out, from your question it appears that you just think it will break.

  • No I haven't tried this out. But, I am going to implement this today in my test environment. – palmon Aug 12 '16 at 7:16
  • Then yes it will 100% work, there will be no issues. The one thing that tends to catch people out is they forget to stop doing log backups via whatever method they normally do (maintenance plans, custom scripts etc). Just before you start configuring log shipping, remove whatever process is doing your existing log backups for whichever databases you are going to log ship. This should ensure a smooth process. – Mat Aug 12 '16 at 9:59
  • Thanks Mat for the response. I am going to implement this in current week. If any issues faced, will post here again :) – palmon Aug 15 '16 at 7:56
1

You START Log Shipping by restoring a FULL, possibly a DIFF and then the transaction log chain. AFTER Log Shipping is setup, it will perform the LOG backups, copy those to the DR server and then restore those on the DR server. All that it uses are the LOG backups. Log Shipping also includes a full backup, but those do not get sent to the DR server. You can have your own FULLs and DIFFs, and those will not impact Log Shipping. Do NOT do your own LOG backups though as that will break the log chain unless you manually apply those to the DR server.

You may want to increase the Log Shipping LOG backup frequency to every 5 minutes or even every minute. It helps to keep the DR site more up to date and gives you more recovery points on the production server.

1

The main step is initialization. You can take a full and/or differential backup of the Production, restore at the DR using that backup and then setup log shipping with no problems at all. You must keep in mind that Production and DR must have the same LSN at the initialization stage.

For the log chain to be broken you would have to do this:
- restore the DR with a backup that was not in order
- a log file got removed from the copy before it gets restored
- bringing the DR database online break the chain

As Tara suggested, 5 min jobs for Backup, Copy and Restore is recommended. Note that the threshold for restores should be set to 3 times the frequency of the 3 jobs (this will save you from a lot of headache).

Below is a useful piece of code for debugging:

--Log Sequence Numbers and Restore Planning
--For restore planning, the most important log sequence numbers (LSNs) are first and last LSNs.
--These LSNs can be obtained from the following locations:
-- --The backupset table in msdb. The columns are named first_lsn and last_lsn.
-- --The RESTORE HEADERONLY statement. The columns are named FirstLSN and LastLSN.
Select *
from msdb.dbo.backupset
  • Thanks Rocklee for the response. I am going to implement this in current week. If any issues faced, will post here again :) – palmon Aug 15 '16 at 7:56
  • Were you able to fix this? – Raidenlee Aug 30 '16 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.