Log shipping large DBs - what about the log?

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:

• Change DB to FULL recovery

• Take FULL backup (5-6 hours) on primary

• Restore FULL backup to secondary (leaving in NORECOVERY)

• Take DIFF backup on primary

• Restore DIFF backup to secondary (still in NORECOVERY)

• 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?

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.

• Keep (or set) DB in SIMPLE recovery model.

• Take FULL backup (5-6 hours) on primary

• Restore FULL backup to secondary (leaving in NORECOVERY)

• Set DB in FULL recovery model

• Take DIFF backup on primary

• Restore DIFF backup to secondary (still in NORECOVERY)

• Initialize log shipping using 'The database is already initialized'

The apparent advantages are:

• No log files to backup during the large FULL backup.

• 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.)

StackExchange Database Administrators Q#107655, answer score: 9