For an instructor lead, in-depth look at learning SQL click below.
Disaster recovery is a critical concern for any organisation that works with data. Today, we shall be focusing on one of such disaster recovery tactics in SQL Server known as ‘Log Shipping’.
What is Log Shipping?
In SQL Server, Log Shipping is a high-availability technique that involves automating the backup of transaction log files on a primary database and subsequently restoring them onto a standby server. This approach not only provides a disaster recovery solution but also aids in offloading reporting activities from the primary server.
Configuration Process
To implement log shipping, there are several steps you would need to follow:
1. Backup of the Transaction Log
|
1 2 3 4 5 6 7 8 |
-- Backup transaction log BACKUP LOG [YourDatabaseName] TO DISK = N'YourBackupPath\YourDatabaseName.trn' WITH NOFORMAT, NOINIT, NAME = N'YourDatabaseName-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 |
2. Copy of The Log Backup
After the transaction log backup, the resultant backup file gets moved to the secondary server. This operation is carried out by a ‘COPY’ job. Here’s a code snippet which simulates the process:
|
1 2 3 4 |
-- Copy job code xp_cmdshell 'copy YourBackupPath\YourDatabaseName.trn YourSecondaryServerPath' |
3. Log Backup Restoration
Finally, the log backup is restored to the secondary database by a ‘RESTORE’ job. The restore operation is typically performed in a ‘standby’ or ‘no recovery’ mode. The following SQL command illustrates this:
|
1 2 3 4 5 6 7 8 9 |
-- Restore transaction log RESTORE LOG [YourDatabaseName] FROM DISK = N'YourSecondaryServerBackupPath\YourDatabaseName.trn' WITH FILE = 1, STANDBY = N'YourStandbyFilePath\YourStandbyFileName.bak', MOVE N'YourDatabaseName' TO N'YourDatabaseFilePath\YourDatabaseFileName.mdf', MOVE N'YourDatabaseName_log' TO N'YourLogFilePath\YourLogFileName.ldf', NOUNLOAD, STATS = 10 |
Conclusion
SQL Server Log Shipping is a powerful and flexible option for database administrators in maintaining high availability and disaster recovery setups. Remember, a good disaster recovery plan can go a long way in protecting crucial business data.