SQL Server Transaction log Shipping and Backup

Stacy Brown ~ Modified: February 28th, 2019 ~ SQL Transaction Log ~ 4 Minutes Reading

Introduction to transaction log Shipping

In our previous section, we have discussed about cluster and noncluster index in SQL Server and understand the difference between them. Now in this section, we will know about the SQL Server transaction log shipping and backups. In transaction log shipping, you can directly send your transaction log backup from your primary database to secondary database on a primary server instance to a separate secondary server instance.

The transaction log backup individually enforces to every secondary database. The Monitor Server, which is the third optional Server instance, keeps all the events such as restore and backup of every status and alerts if one of these operations are scheduled due to a failure.

SQL Server Transaction log shipping

Primary server: This is the production server of your SQL Server.

Secondary server: The Server where you keep the copy of your primary database.

Monitor server: This Server monitors all the log shipping details of both the primary and secondary Servers. It alerts when a failure has occurred.Take a look at here to know how to configure Transaction log shipping in Server.

Log shipping in databases

Primary database: This is the database of primary Server, which backup you want in secondary server. All this execution is completed from the primary database through SQL Server Management Studio.

Secondary database: This is the copy of your primary database, which is in either RECOVERING state or the STANDBY state

Note: The Monitor Server cannot be changed once it has been configured. It can change only by removing log shipping.

In SQL Server log shipping and backups, above we have discussed transaction log shipping. Now below you will get to know about the log shipping backups.

Transaction log shipping-Backup and Copy

The SQL Server agent who performs the backup job such as keep all the history of local server and monitor server and delete old Transaction log backup information. The Backup of Log Shipping is built on the primary server instance, once the log shipping is enabled. Also, from the primary server instance to a configurable destination on the secondary server, the SQL Server agent copies all the backup files and logs all the history on secondary and monitor server. The copy of log shipping is built on each secondary server once the log shipping is enabled on a database.

Transaction log shipping-Restore and Alerts

The restore job of a SQL server agent is to restore all the copied backup files to secondary databases and log all the history on local and monitor servers, also it deletes all the old history. Log Shipping Restore built on secondary server once the log shipping is enabled on the database. Similarly, the alert job of the SQL Server agent raises when any of the operations (backup or restore) fails in primary database and secondary database. This alert built on the monitor server once log shipping is enabled on the database.

Advantages of log shipping in SQL Server

SQL Server transaction log shipping is helpful in case of disaster.
It provides a recovery solution for a single database and for secondary databases, whether it is on a separate instance of SQL Server.
The log shipping is helpful in accidental changes.
The alert job in log shipping is helpful in case of transaction failure between primary and secondary databases.
The log shipping is easy to set up and can be maintained easily.

Conclusion

Log shipping is helpful when we cannot take regular backup of our database. It maintains the backup of primary database which can be helpful at the time of disaster and also it increase the database availability. SQL Server transaction log shipping provides a better way to keep regular backups of your SQL Server database. This way SQL Server log shipping and backups are useful.