8

I'm currently setting up log shipping of a large DB (Approx 1.5TB) and am wondering what I can do about the log file.

As it stands, I want to do the following steps:

  1. Change DB to FULL recovery
  2. Take FULL backup (5-6 hours) on primary
  3. Restore FULL backup to secondary (leaving in NORECOVERY)
  4. Take DIFF backup on primary
  5. Restore DIFF backup to secondary (still in NORECOVERY)
  6. Initialize log shipping using 'The database is already initialized'

The issue is that while I am taking the full backup, the log file will fill up faster than the backup(s) can complete.

What options do I have to keep the log file from filling up? Can I just take log backups as normal during the FULL backups as the DIFF restore will cover any transactions that take place during that timeframe? Has anyone done this before with a DB of this size, any tips/tricks to make it easier?

9

If I understand you correctly, your major problem is the log files during the several hours of backup. From your opening statement I understand that the 1.5 TB database normally runs in SIMPLE recovery, and thus no log backups to do.

Disclaimer: I have never done log shipping on this scale.

Of course, you should ask whether you can get more space allocated for your log files. If you can, then great.

However, I think a small modification of your plan, providing that you already run in SIMPLE recovery model and/or the risk of SIMPLE recovery model for a few hours is worth it, would ease some of your worries.

  1. Keep (or set) DB in SIMPLE recovery model.
  2. Take FULL backup (5-6 hours) on primary
  3. Restore FULL backup to secondary (leaving in NORECOVERY)
  4. Set DB in FULL recovery model
  5. Take DIFF backup on primary
  6. Restore DIFF backup to secondary (still in NORECOVERY)
  7. Initialize log shipping using 'The database is already initialized'

The apparent advantages are:

  1. No log files to backup during the large FULL backup.
  2. Switching to FULL before beginning the DIFF backup will give you the needed log to start with and its longest growth is probably during the DIFF backup.

Regarding when a log backup can start:

https://technet.microsoft.com/en-US/library/ms190729(v=SQL.105).aspx

This says: "A new log chain starts either with the first full database backup following database creation or after switching from the simple recovery model to the full or bulk-logged recovery model."

So, I still believe that this will work as outlined. (Not identical, but I have used a differential backup to cover a gap when log files were lost, so as to establish a new origin for the log backups.)

(Remember my disclaimer, of course.)

  • I think that sounds like it will work. I'm currently taking the FULL backup now and will report back tomorrow with the results. Thanks for the help RLF, I'll mark it as answer as soon as everything is done, in case we have a step or 2 to add for future readers with this issue. – Kris Gruttemeyer Jul 21 '15 at 18:09
  • Wait, doesn't FULL recovery not activate until the first FULL backup is taken? I'm wondering if switching to full recovery after the first full backup will have no effect since full recovery doesn't activate until after the first backup. – Kris Gruttemeyer Jul 21 '15 at 19:54
  • @KrisGruttemeyer - updated post – RLF Jul 21 '15 at 20:04
  • Copy that, I'll report back tomorrow. This thing is still backing up. – Kris Gruttemeyer Jul 21 '15 at 20:19
  • Make sure you have backup compression on... – Rob Farley Jul 22 '15 at 0:25

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.