Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Log Shipping


By:   |   Read Comments (15)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Log Shipping

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


Problem
There has been a lot written about SQL Server Log Shipping as well as several scripts and techniques for implementing this process.  Basically Log Shipping is the process of backing up your database and restoring these backups to another server for failover.  To keep the data current and to minimize risk of data loss the process involves using SQL Server transaction log backups. By using transaction log backups the size of the backups are much smaller than full backups and the restore process can be done on a continuous basis.  So how do you implement Log Shipping?

Solution
There are several ways of implementing Log Shipping.  Some of these processes are built into SQL Server and others can be implemented by using third party tools.  Let's take a look at some of the options that are available.

SQL Server 2005
In SQL Server 2005 the process of implementing Log Shipping is embedded with the Workgroup, Standard and Enterprise editions of SQL Server.  To implement it, right click on a database name and select Properties.  On the properties screen there is an option for Transaction Log Shipping which looks similar to the screenshot below. From here you can follow the step by step process to set this up.

SQL Server 2000
In SQL Server 2000 this feature is only available in the Enterprise or Developer editions of SQL Server. This does not exist in the Standard or MSDE editions.  To enable Log Shipping with this version, this is setup using the Database Maintenance Plan Wizard and selecting "Ship the transaction log to other SQL Servers (log shipping)" at the bottom of the wizard screen.  The following screen shot shows you how you can select this option.

Third Party Tools
In addition to using the built in functions within SQL Server there are third party tools that have built-in log shipping functions.  Some of these tools include:

Scripts
In addition to the built-in tools and third party tools, there are several implementations that exist so you can implement your own Log Shipping strategy.  As I mentioned above the process is as simple as creating a log backup and restoring it to another instance of SQL Server.  This can be as manual or automated as you make it. Here are a few links to other articles that exist on the net that show you how you can setup Log Shipping on your own versus using the built in tools or other third party tools.

Next Steps

  • Log Shipping is a pretty simple process to setup and use, so take a look at it closely to see if it makes sense for your environment.
  • Look at the scripts that are available on the net to see if they are something you can use or modify to make Log Shipping work for your database servers
  • Take a look at the new Database Mirroring feature in SQL Server 2005, this may be a simpler solution to setup a failover server instead of using Log Shipping


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, December 10, 2013 - 12:34:01 AM - SHAM AFrIth Back To Top

Hi Greg !

Thanks For Your Earlier Reply !

Restore Policy in most Companies are > Restore 1. Full (Last:Recent) Backup, 2. Differtial (Last:Recent) Backup, 3. Restore Log Shipping.

I Think They Restore all the database members. finally they are updating Logs Tranasactions by log shipping. Log Shipping only have a recent backup (Meant Max 15 mints Later backup). then why they used to restore full + Differential backup ? are they set backup only for log files ( to avoid grow log file size ). If We Used Logshipping with ( data + Log ) backup in large scale db like (250 GB). howlong time logshipping would take ? and more doubht is everytime logshipping take a full backup and restore into secondary server? Can we setup logshipping for taking backup of only log files?


Monday, December 09, 2013 - 5:26:50 PM - Greg Robidoux Back To Top

Hi Sham,

When you setup log shipping you specify how frequently you want to backup the transaction log and when the log backups should be applied to the secondary server.  So this is where you can setup the 15 minute schedule as you mentioned.

In addition, you will still want to create full backups on your primary server just case there are any issues with both the primary and the secondary servers.  Any log backups you create after the full backup can then be used along with the full backup to do a restore on a totally different server.


Monday, December 09, 2013 - 12:18:54 PM - SHAM AFrIth Back To Top

 

Dear All,

 

Acutlly I am in big confusion, If i setup for the logshipping with secondary server act as recovery mode of Read-only. If I dont have any other backup plan for full, incrimental backup then? still logshipping could make secondary database will upto date ? If My Database got crash meant can i restore all members by Logshipped secondary server? Exactly Am I asking is logshipping is quite enough for Full backup? I Know No is the answer. So My Confusion is then how secondary server would get update in scheduled interval timings Ex:15 mints schedule logshipping plan?

 

 


Wednesday, January 30, 2013 - 1:07:16 PM - Greg Robidoux Back To Top

@happy - Log shipping basically takes the backups that you do on one server, copies the backups to another server and then restores the backups so you have a warm standby server ready for failover.  There are other things you will need to do for the failover, but that is the difference.


Wednesday, January 30, 2013 - 10:54:08 AM - happy Back To Top

Dear Sir.

log shipping means create backup if one server failure then use to log shipping restore the data and in log shipping data backup smaller other backup. what is a main differeent log shipping or other backup. am i true or not


Friday, July 06, 2012 - 8:26:15 AM - Greg Robidoux Back To Top

Nitesh - one option is to use FTP to deliver the backup files.  You can setup your primary server to create the backups and then FTP them to your secondary server.  And on the secondary server you can have a job that looks for new backups to restore.  Hope that helps.


Friday, July 06, 2012 - 7:21:45 AM - Nitesh Back To Top

I am having two servers A and B A is in my local network while Server B is on remote location can be access using Remote desktop using Public IP now I would like to configure log shipping between these two servers please help ASAP.


Sunday, September 12, 2010 - 2:32:29 AM - Sagar Back To Top
Hai

How to setup log shipping between Enterprise and Standard versions in SQL Server 2008.

Thank you in advance for your answer.


Tuesday, August 24, 2010 - 3:49:58 AM - agnnga Back To Top
Hello,

I am back with my questions of HA/DR. I've been searching for more details on how complicated it could be to setup and to manage replication. Unfortunately I couldn't find one.

1, Could you please tell me more precisely why it is more complicated in setting up and managing(in what criterion ...)?

2, I would like to know more about the performance and the broadband of each technologies (that I have not managed to find intersting documents on the subject, again!). Do you mind telling about it or juste have any ideas where I can find related information.

Thank you very much and have a great day.


Thursday, August 05, 2010 - 11:18:20 AM - grobido Back To Top
For a DR I would recommend using log shipping or database mirroring.  Replication is not a good choice because it is more complicated to setup and to manage.  And yes you are correct you will need a primary key on all tables.

The only potential loss of data with log shipping would be the tail of the transaction log if this is not backed up and restored to the secondary site.  Other than that all transactions will either roll forward or roll back to put the database in a consistent state when it is recovered.


Thursday, August 05, 2010 - 9:54:33 AM - agnnga Back To Top
Yes, that is one of the two problems of log-shipping. The second one is that while performing the restore, it might result in data loss as well. The % of data loss has mentionned in the docs that I've found so I have no idea whether it can be tolerated or not.

So yes, I totally agree with you why they are used together. But then, for example

- we can't implement replication because there isn't a primary key for all the tables (and it is required for replication)

- we can't set up a mirroring solution because it can't be done between a Standard and a Enterprise version

=> log-shipping's the only choice?

Indeed, if replication is utilisable, what are the advantages by mixing log-shipping and replication? Is that because replication has table-level and log-shipping has data-base level in copying data? MSDN said that it is more complicated as well comparing to log-shipping in the implementation. Do you know anything about that more precisely? Personally, I've tried replication and have never managed to make it work due to "the agent has never been executed"

Thanks a lot!

 


Wednesday, August 04, 2010 - 10:42:42 AM - grobido Back To Top
Yes you can mix and match all of these technologies.  With mirroring you can only mirror to one other server, so if you need to have two standby sites ready, a local one using database mirroring and a remote one using log shipping this is why you would use both.  Also, replication could be used for a reporting instance to offload reporting from your production server.  With database mirroring you can use a database snapshot for reporting, but the data is only as current as the last snapshot.  For log shipping you need to have exclusive database access to restore a transaction log, so users would need to be out of the database while performing the restore, this is where replication comes into play for reporting, because users can still access the database while transactions are replicated.

As far as  overhead goes you would increase the network bandwidth needs if you used all three at the same time, but that is the price to pay if you need all of these options.

Wednesday, August 04, 2010 - 9:48:58 AM - agnnga Back To Top
Thank you!
There is another issue about merging different techniques. It is said that we can mix one with the rest of all backup solutions such as : replication(SAN or transactionnal), log-shipping, failover clustering and mirroring in order to gather all the advantages of each solution. Does that mean we'll have all of the inconveniences as well? More precisely, if we implement mirroring and log-shipping together, we'll have to have 2-same-version-server SQL (required by mirroring system) ?
And while comparing those solutions, the do***ents have hardly mentionned about the differences in performance or rate-level (broadband?). I would like to know more about those criterions, please?
Thank you in advance for your answer.

Tuesday, August 03, 2010 - 7:30:40 AM - grobido Back To Top
Yes you can setup log shipping between Enterprise and Standard versions in SQL Server 2008.

Tuesday, August 03, 2010 - 4:17:29 AM - agnnga Back To Top
Hello,
I would like to know whether it is possible to implement log-shipping between an instance in SQL Server 2008 Standard and the other in Enterprise version please?
Thank you in advance for your answer.

Learn more about SQL Server tools