Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012).

At first i took a full backup of the first one and restored it on the other one and than i set up the log shipping. The databases first backup-restore took a few hours and below are my questions that I need to understand :

  • How does the log shipping process knows from when to start the logs it is shipping?
  • Does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?
  • Can i be sure that no information was lost in these few hours ?
share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012)

This is a ONE WAY situation.

When you have to failover, it will be easy as the logs from sql server 2008 can be restored to sql server 2012.

The stopping stone will be FAIL BACK - if sql server 2008 is Primary and SQL Server 2012 is secondary (standby) server, after failing over to sql server 2012, you wont be able to failback.

Note: SQL Server allows you to restore transactions logs from earlier versions of the product to higher vesions but the upgrade doesn't happen until recovery of the database occurs. And that's why you can't use STANDBY mode in this situation. It has to be in RESTORING mode only.

The only option for failback will be to upgrade the old sql server 2008 to 2012.

At first i took a full backup of the first one and restored it on the other one and than i set up the log shipping. The databases first backup-restore took a few hours.

Logshipping is an automated process of doing FULL backup, copying it to secondary server and restoring it in NO-RECOVERY state and then taking consequent log-backups from the primary server, copying them to secondary server and restoring them.

Depending on the size of the database backup (whether you are using compression or not) and the network bandwidth, it can take from mins to hours. Other way is to initialize secondary database from the FULL backup of primary database - restore an existing backup of the primary database to the secondary database

i wish to know: how does the log shipping process knows from when to start the logs it is shipping?

The history and status of log shipping operations are always saved locally by the log shipping jobs. The history and status of the backup operation are stored at the primary server, and the history and status of the copy and restore operations are stored at the secondary server. If you have implemented a remote monitor server, this information is also stored on the monitor server.

Refer to : Monitor Log Shipping for more details about the tables and stored procedures used to track log-shipping jobs and status.

does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?

It starts when you initialize the secondary database from the full backup of primary database restored with NO RECOVERY on the secondary server. Logshipping proces relies on the concept called Log-sequence number (LSN's) to link which log backup will be restored after the last log backup.

A picture is more than a thousand words !

enter image description here Reference

Can i be sure that no information was lost in these few hours ?

Yes there would be no information lost - unless in these few hours the primary server crashed or the backup becomes corrupted (due to I/O subsystem). As soon as the full backup is restored, logshipping will continue taking log backups on primary and start copying it to secondary server and restoring them.

The minimum backup/copy and restore frequency is 1 min.

Note: Once you configure a database for logshipping, you dont have to take any additional log backups - as this will break the LOG CHAIN and the logshipping will start failing.

Full and differential backups can be taken, but NO log backups.

Read up on more on How to Perform SQL Server Log Shipping BY BRAD MCGEHEE

share|improve this answer
    
Thank you for such a great detailed answer. i highly appreciate it. –  Matan L Jul 16 '13 at 11:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.