0

I want to use logshipping to transfer large databases (400-700 GB size) from Server (and storage) A to Server (and storage) B.

I tried this several times and it worked. But I have questions about some details:

While using LogShipping, the only Log-Backups that can be taken are the backups that the logshipping process creates. So other backup software or jobs have to be disabled in advance.

In case I setup log shipping, the initial fullbackup that is required, independantly wether I take and restore it manually in advance of the logshipping setup or let the logshipping setup create and restore it for me, will take a while, at least one hour or more, to be finished, and additional time to be restored on the secondary. I have already tried both options with success for smaller dbs.

My question is: If I set up logshipping for a large db and need to stay able to do a point in time recovery, what will happen in the time between taking the initial full backup and the first log backup taken after the restore of the full backup has completed? Is there something like a gap in backup? Before the setup of log shipping is completed, I guess there is no backup configured at all, so will I lose thios period of time? Or will the transactionlog just keep growing until the full-backup-restore part has completed and the first log backup happens?

  • 1
    You can keep taking log backups on the primary server. When you set up log shipping restore full with no recovery and all the log backups with no recovery. But if you use the GUI you are correct, your logs will keep growing until the full backup restore completes. – SqlWorldWide Mar 27 '17 at 16:25
  • I'm very confused. I tried the following: 1.Removed any backup jobs 2.Created a new TL backup job 3. Created a new Full Backup of the DB 4. Restored this full BAK on secondary with norecovery (took about 1 hr 5. Setup Logshipping with Secondary initialized 6. stopped the Log backup (of step 2.) RESULT: On the secondary only TL Backup created by Logshipping arrived. The ones created after full backup and completion of the full backup resore (betw. 3. & 4.) were ignored... Now, are those missing on secondary??? – Magier Mar 28 '17 at 9:42
  • The restore job says: "Could not find a log backup file that could be applied to secondary database 'TLSDB'. The restore operation was successful. Secondary Database: 'TLSDB', Number of log backup files restored: 0" - I copied the TL backups to the destination folder on 2ndary manually hoping it will take them. But they are completely ignored. – Magier Mar 28 '17 at 9:54
  • 1
    Please read this brentozar.com/archive/2015/12/… – SqlWorldWide Mar 28 '17 at 14:41
  • 1
    I asked this exact question here: dba.stackexchange.com/questions/107655/… – Kris Gruttemeyer Mar 30 '17 at 18:51
1

You can keep taking log backups on the primary server during full backup and restore to secondary. When you set up log shipping restore full with no recovery and all the log backups (taken during full backup restore) with no recovery. When you are caught up you initialize log shipping.

|improve this answer|||||
  • In my Tests only the logbackups that were taken AFTER completion of the full Backup could be restored to the 2ndary. – Magier Mar 30 '17 at 19:25
  • agreed. I edited my answer. that is also explained in the article I refereed earlier. – SqlWorldWide Mar 30 '17 at 20:21

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.