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?

Improve this question
2
  • 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, 2016 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, 2016 at 7:56

3 Answers 3

Reset to default
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.

Improve this answer
3
  • No I haven't tried this out. But, I am going to implement this today in my test environment.
    – palmon
    Aug 12, 2016 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, 2016 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, 2016 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.

Improve this answer
3
  • Thanks Tara for the response. I am going to implement this in current week. If any issues faced, will post here again :)
    – palmon
    Aug 15, 2016 at 7:56
  • I have configured it and it is working fine. Thanks all for your help :)
    – palmon
    Oct 14, 2016 at 10:09
  • @palmon please have a look here to check how the site works
    – Marcello Miorelli
    Apr 12, 2017 at 21:11
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
Improve this answer
2
  • 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, 2016 at 7:56
  • Were you able to fix this?
    – Raidenlee
    Aug 30, 2016 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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