Sql-server – Resume Logshipping after Primary database restored

If you don't include the transaction log, then it's not really log shipping, is it?

Your full backup is restored to that point in time, and that's where it will stay until you either (a) apply a log or (b) apply a differential or another full backup. Yes, the restore does also bring over the transaction log, but only the state of the transaction log at that same point in time. I guess technically you could argue that you shipped the log once, but the point of log shipping is to keep the secondary server up to date more often than the full backups (which are typically very large in comparison to periodic log backups).

So if your intention is to do this backup / restore once a day, and you don't need to keep the copy up to date throughout the day, just do a simple backup and restore, and stop calling it log shipping.

If you want the copy to stay relatively up to date throughout the day, then yes, it will be mandatory to take periodic log backups on the primary server and restore them on the secondary server. Note that you have to weigh what's more important: keeping a copy of the data up to date, or letting users query stale data uninterrupted. If you restore a log to the secondary server every 15 minutes, you have to kick all the read-only users out every 15 minutes, because you need exclusive access to the database in order to restore the log.

You can read more information about log shipping here:

http://msdn.microsoft.com/en-us/library/ms187103(v=sql.90).aspx

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