Blog

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!

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

    Thanks.

    • 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 http://DBA.StackExchange.com. 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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php