SQL Server Log Shipping

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


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.

logshipping1

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.

logshipping2

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




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

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 9, 2013 - 5:26:50 PM - Greg Robidoux Back To Top (27740)

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 9, 2013 - 12:18:54 PM - SHAM AFrIth Back To Top (27739)

 

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 (21800)

@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 (21796)

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 6, 2012 - 8:26:15 AM - Greg Robidoux Back To Top (18372)

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 6, 2012 - 7:21:45 AM - Nitesh Back To Top (18368)

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 (10154)
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 (10070)
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 5, 2010 - 11:18:20 AM - grobido Back To Top (10017)
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 5, 2010 - 9:54:33 AM - agnnga Back To Top (10016)
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 4, 2010 - 10:42:42 AM - grobido Back To Top (10012)
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 4, 2010 - 9:48:58 AM - agnnga Back To Top (10011)
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 3, 2010 - 7:30:40 AM - grobido Back To Top (10007)
Yes you can setup log shipping between Enterprise and Standard versions in SQL Server 2008.

Tuesday, August 3, 2010 - 4:17:29 AM - agnnga Back To Top (10006)
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.














get free sql tips
agree to terms