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 then I set up the log shipping. The database's first backup-restore took a few hours and below are my questions that I need to understand :
- How does the log shipping process know 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 ?
Best Answer
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.
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
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.
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 !
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