Log shipping is a tried and true method in SQL Server for keeping a copy of your data on a secondary server. If you haven’t worked with this feature yet, join Jes to learn the basics, such as what log shipping is used for, what the moving pieces are, and how to set it up in this 30-minute video:

Want to learn more about monitoring and failing over? Watch Part 2: When Disaster Strikes!

Want to know more about backups?

Grab my Backup & Recovery Step By Step training for more information on why backups and restores are so important, and how to perform them!

Jes Schultz Borland
↑ Back to top
  1. One of the discussion/question at the end was, Is it possible to run a separate Transactional Log backup along with the LS_backup job that the Log Shipping setup with create ? I’ve had problems before on the restore job when setting up parallel transactional log backup jobs like these. How to you setup this new log shipping backup job if you already have a Transactional log backup from your daily Maintenance Plan ?
    Please suggest…

  2. Pingback: Something for the Weekend - SQL Server Links 29/02/13 • John Sansom

  3. Pingback: Something for the Weekend – SQL Server Links 29/02/13 - SQL Server Blog - SQL Server - Telligent

  4. Pingback: Log Shipping FAQ - SQL Server Blog - SQL Server - Telligent

  5. Great webcast. Thanks.
    I have a question; In my existing enviroment we have SP dbs in db Mirroring setup. Now to move to our new COLO we are planning to setup Log Shipping above the exsiting Mirroring setup. All the dbs are in Full recvoery mode and now to create the new LS scenario I have to take a full backup and log backup to start the LS. How will it impact my recovery capablity, as this might break my log chain?
    Is this a possibility at all?
    Thanks for you time.
    Sri E

    • To set up log shipping, you can specify a new full backup, or an existing full backup. However, log shipping will need to replace your current transaction log backup job. You can have two jobs take log backups, but if only one of those sets of backups is restored to the secondary, the log shipping chain will be incomplete. It’s best to have log shipping be your only log backup for the time being.

      • Hi Jes,

        I have setup log shipping to a secondary database and its all working fine.

        My question is also related to getting a full backup to storage. I am confused regarding how this can be done when log shipping is already setup.

        I have read that it is not possible, that it is possible, that only tx logs should be backed up etc.

        Ideally I would like a nightly full back up into bak files of my production databases to remote storage in addition to the using log shipping to get quickly get a recent version of the production databases back online. Can you advise how this is done?
        Thanks, Conor.

        • Conor, you can and should take regular full backups of your database, and store those backups somewhere other than the database server. Taking full backups will not affect your log shipping chain. Transaction logs have an LSN – log sequence number – that is very important. If you have multiple processes taking transaction log backups, you must be very careful about how those logs are applied. However, full backups won’t affect the log backup chain.

  6. Hi Jes,

    I like to think about the log shipping as being more on the high availability side than on the disaster recovery side.
    What do you think?

    P.S.: I enjoy watching your video, congratulations.

    • Vitor – if you use log shipping for high availability, how do you manage failover when the primary box dies? And how do you fail back?

      • I like to think of log shipping as the “modest” high availability and the always on as the “rich” high availability. It all depends 🙂 on how much you want to spend and how much time you can be offline.

        I’m not saying that is wrong to say that log shipping is a disaster recovery process.
        I don’t like to think of log shipping as a disaster recovery process because it may lead to other people to think that the backups aren’t needed any more.
        “Why do we need? We have the data on another server.”

        And in my experience the disasters I had to deal with where issues related with someone, or a process, that mess up the data and if you don’t know what as happened as soon as it happens then the secondary server will have the same problem.

        • Vitor, one of the benefits of log shipping and the second copy of the data relates to your last point. There have been cases where someone has dropped a table or deleted an important record, and because the second copy of the data exists, it’s been retrieved. Yes, this does involve finding out about the issue before the affected log is restored, but it can happen.

          Log shipping can be a high availability solution, but without the automation factor I wouldn’t use it as an HA solution for my important data. I focus on log shipping as a DR solution, because that is where it generally fits in most business models.

  7. Really!!!.Great to see the videos, It’ will be helpful if you mentions the what real time issues from your experience.

  8. Hi gang, not getting any sound on this one? Is it just me? Part 2 works fine…

  9. Hi,
    Thanks for providinh the great info on log shipping.

    But i do have a concern in implementing Log shipping in one of our environments.
    We have a 3.5 TB DB which is already involved in log shipping to two different server, as one will be in restore sate only and one will be in stand by mode which helps for reporting need. all source and destinations are sql 2005.

    Now we want to implement log shipping to third server which host sql 2008 and we are not able to do it without downtime. We restored a full backup of primary db on the 2008 server and it took 2 hours to restore and later on we took a differential back up to of primary (since we have to many log backups which run every 15 mins) and restored it and started log shipping. Here it fails by skipping the log file since it is looking for previous log file which is already shipped to other servers.

    Finally we stopped log backups and followed the same procedure it succeeded but it took 3 hours downtime which we don’t want to do since it is a prod server. How can we implement log shipping in this scenario? is it possible to implement with out any downtime.

    Please provide your valuable suggestion.


    • Harsha – Yes, it’s possible to do this without any downtime, but troubleshooting this is kinda beyond the scope of what we can do quickly in a blog post comment. It would take an hour or two of working with you. If you’re interested in consulting help, you can click Contact at the top of this page, or you can also try posting this question along with as much details as possible at Thanks!

  10. I ran through the steps on the video 5 times in a row. The full backup/restore worked, but the transaction logs did not even get transferred to the secondary. I then checked the jobs on the secondary server and both the copy and restore jobs had a server listed that was not the primary or secondary. After changing the server names on the secondary jobs, the jobs completed successfully. I built the scripts using the wizard in SSMS. When I checked the code, the new server did not appear in the script.

  11. Hi Brent and Jes,

    Thank you for this helpful resource.
    I’m seeking an answer to these questions below and appreciate your reply:
    Imagine a log shipping scenario from SQL Server 2008 (10.00) (primary) onto SQL Server 2008 R2 (10.50) secondary. What I wonder is:
    1) Is it possible to restore log backups taken from 2008 onto 2008 R2?
    2) Is it possible to revert back to 2008 as primary database after a failover onto 2008 R2?

    Thank you.

    • Sqlism – sure, here you go:

      1. Yes.
      2: No.

      I feel bad with such short answers, but there you go! Log shipping between versions is usually used as an upgrade mechanism from your old to new servers, but it’s a one-way affair.

  12. Hi Brent,

    Thank you. Short answers make it even more clear.
    Have a nice day.

  13. Hi Brent ,
    In case of a disaster, I should revert back to my primary after it restores and comes online because its specifications are better than our secondary.
    1) Is it possible revert back to 2008 as primary database after a failover onto the same version (2008)?
    2) My primary build is 7601 Service Pack 1 whereas secondary is 7600. Does this build difference matter during a failover or when reverting back?

    Thank you.

  14. We are trying to do log shipping to our secondary sites over the WAN from our primary data center. The connection speed between the primary and secondary sites is 20 MB, the total sizes of databases that would include in log shipping is around 2TB, and average log size/hour is around 1.5-2GB.
    Question is would it be possible to do the log shipping with current WAN we have given the size of data we try to log ship.
    Please advise.
    Thank you.

    • Do you mean 20 MB/second for the connection speed? Let’s do some maths. 2 GB = 2,000 MB. If you have 20 MB/sec speed – max – that’s 1,200 MB per minute, or 72,000 MB/hour. Theoretically, yes, it would work. First, though, how do you plan to move the entire database? 2TB is a lot of data to move across a network. Also, is this a dedicated connection, or is that 20 MB/sec shared with other servers? Is that a guaranteed rate, or theoretical max? Your average log size may be 2 GB/hour, but what about during data loads or index maintenance – do you know how large the files will get at that time, and is it OK if log shipping gets behind during those times?

      These are just a few things to consider!

    • In my experience, you will need to courier the initial database to the remote site on some external drive (preferably yourself). This usually takes a couple of days to pull off. During transport, you could already be manually shipping copies of your tran logs so they will be available at the target site when the backup arrives (vs. needing to catchup the 100GB worth of transactions that collected during the 2 days). If your connection speed is truly 20 M[Byte], that’s one thing, but if it’s 20 M[bit], then that’s 8 times slower, and 100GB could take several hours to push.

      This scenario probably doesn’t lend itself well to the log shipping wizard, and might require more manual scripting like what can be found in the Wrox SQL Admin books. Or some consulting hours from Brent Ozar ULTD might also prove useful 😉

  15. Hi ,

    Do have any video or document which explain step y step how to install service pack on database which using log shipping ?


Leave a Reply

Your email address will not be published. Required fields are marked *