Currently, there are many disaster recovery (DR) technologies in the industry, including database mirroring, clustering, and replication solutions. Log shipping, however, is a simpler and easier-to-configure and maintain method. This article will discuss SQL Server disaster recovery steps with log shipping.
Disaster Recovery Solution with Log Shipping
Log shipping primarily maintains backups on a secondary server and takes over the primary server as needed to improve overall database availability. In other words, when the primary database becomes unavailable due to a disaster, you can manually bring the secondary database online to continue providing services.
To configure log shipping for a database, SQL Server creates the following three agent jobs to automate backup, copy, and restore operations:
The first job runs on the primary instance. It backs up the transaction log on the primary database.
The second job runs on the secondary server. It copies the log backups from the primary server to the secondary server.
The third job also runs on the secondary server. It restores the log backups and updates log entries on the secondary database.
Consideration before Configuring Log Shipping in SQL Server
Although configuring log shipping is not difficult, several considerations must be taken into account before implementation:
Database-level protection: If you only need to protect a small number of databases in the event of a disaster, this level is sufficient. However, if you want to preserve multiple databases at the SQL Server instance level, a standalone log shipping solution is inadequate.
Manual failover on the secondary server: Log shipping alone cannot automatically failover from the primary server to the secondary server. You must manually bring the secondary database online.
Manual SQL login configuration: SQL logins are not automatically transferred from the primary server to the secondary server. You need to transfer login credentials from the primary server instance to the secondary server instance for login synchronization. Additionally, you often need to manually create various maintenance plans, linked servers, and SSIS (SQL Server Integration Services) packages on the secondary server.
Risk of data loss: Typically, when the primary database becomes unavailable, only the last transaction log backup can be restored. This means that any transactions occurring after the last log backup was sent to the secondary server will be lost. For example, if the primary server fails at 9:00 AM and the last backup copied to secondary server instance B was at 8:45 AM, then all data from 8:45 AM to 9:00 AM will be lost.
Reverse log shipping: This is useful when switching server roles without performing a full database backup. For instance, if you have a large backup and need to transfer data from the secondary server to a remote primary server, copying the entire backup might take a long time.
How to Configure and Use Log Shipping?
Typically, the process of configuring log shipping can be divided into two distinct steps:
Step 1 – Initialize the Database on the Secondary Server
Assume we have two databases on the primary server instance, and we need to ship logs for TestDB1 to a secondary server that initially has no databases. It is important to note that to set up log shipping, the database must be in FULL or BULK-LOGGED recovery mode. If the database is in SIMPLE recovery mode, log shipping will fail because transaction log backups cannot be used.
1. First, we need to perform a full database backup and a transaction log backup. You can run the following T-SQL queries to create “full” and “transaction log” backups:
backup database TestDB1 to disk = 'c:\backup\TestDB1.bak'
backup log TestDB1 to disk = 'c:\backup\TestDB1.bak'
2. Next, restore the backup on the secondary server.
3. In the Restore Database interface, select Device as the data source and click its icon.
4. In the Select backup devices dialog box, click Add.
5. Select the backup file to restore and click OK.
6. Execute the restoration for the TestDB1 backup.
7. Click Select a page and go to Files to modify the physical database file locations if necessary.
8. Then, click Options on the left. On the Options page, choose RESTORE WITH STANDBY from the Recovery state dropdown list. Note that selecting RESTORE WITH STANDBY ensures the database remains in a read-only state. If you choose RESTORE WITH NORECOVERY, the database will be inaccessible.
9. After selecting the appropriate recovery state, click OK to ensure a successful restoration. This will restore TestDB1 in Standby (read-only) mode on the secondary server instance.
At this point, the database has been successfully initialized on the secondary server.
Step 2 – Enable the Primary Database
1. Right-click TestDB1 on the primary server instance and click Properties.
2. Select Enable this as the primary database in the log shipping configuration.
By default, the transaction log is backed up every 15 minutes. However, sometimes the transaction log can grow too large to be copied and restored within the predefined time limit. To address this, you need to schedule additional log backups. Click Backup Settings, specify the backup file location in the Transaction Log Backup Settings interface, then click Schedule, and change the backup frequency to run every 1-2 minutes.
3. Click Add to set up the secondary database. The system will prompt you to connect to the secondary server instance.
4. As configured in Step 1, in the Secondary Database Settings interface, select No, the secondary database is initialized.
5. Proceed to copy files by specifying the secondary server's backup folder location, setting the backup frequency, and clicking OK.
6. In the Restore Transaction Log interface, set the database state to Standby mode and check Disconnect users in the database when restoring backups. After setting the backup interval, click OK.
7. To add the secondary server instance and database, click OK to create SQL Server agent jobs.
Under the SQL Server Agent on the primary server, you will find the transaction log backup job.
Under the SQL Server Agent on the secondary server, you will find two newly created jobs: one that copies transaction log backups from the primary database and another that restores the transaction log backups on the secondary database.
8. At this point, the disaster recovery solution with log shipping is fully configured. If the primary database fails, you can immediately bring the secondary database online. You can also run the following query to confirm that the secondary database has exited standby mode:
Select * from Products
RESTORE DATABASE TestDB1 WITH RECOVERY
9. By refreshing the database, you will see that TestDB1 on the secondary server is now online.
More Comprehensive Data Protection Strategy for SQL Server
While implementing disaster recovery solutions such as log shipping, businesses often require a more comprehensive and efficient data protection strategy. Vinchin Backup & Recovery offers an automated backup and recovery solution specifically designed for VMs and databases like SQL Server, supporting full, incremental, and differential backups. With built-in data deduplication and compression technologies, it significantly reduces storage consumption and backup time.
Additionally, Vinchin’s backup solution eliminates the need for complex manual intervention, enabling automatic database protection while supporting cross-platform migration and cloud storage. In the event of an SQL Server failure, Vinchin helps administrators quickly restore databases, preventing data loss and prolonged downtime, thus offering a more comprehensive disaster recovery guarantee for enterprises.
To create SQL Server database backup jobs, please go to Physical Backup > Database Backup > Backup page:
1. Select the databases which need to be backed up.
2. Select a backup node on which you want the backup data to be processed and stored.
3. Configure backup strategies according to your needs.
4. Review and confirm the settings.
Click the button below to try Vinchin's 60-day free trial to experience an efficient and reliable data backup and recovery solution!
SQL Server Disaster Recovery FAQs
1. What is the difference between High Availability (HA) and Disaster Recovery (DR)?
HA ensures minimal downtime using failover clustering, Always On Availability Groups, or database mirroring, while DR focuses on restoring service after catastrophic failures, often involving offsite backups or secondary data centers.
2. What is a SQL Server Failover Cluster Instance (FCI), and how does it help with DR?
An FCI is a high-availability solution using Windows Server Failover Clustering (WSFC) to provide automatic failover at the SQL Server instance level. It requires shared storage (SAN, Storage Spaces Direct, or cloud-based solutions). It is ideal for on-premises high availability but not a DR solution alone, as it does not protect against site-wide failures.
Conclusion
Log shipping is an economical, efficient, and simple disaster recovery solution for SQL Server. It is an ideal choice for database-level disaster recovery. However, for instance-level disaster recovery, other DR techniques such as database mirroring or failover clustering should be considered. Additionally, log shipping may lead to data loss. If you need to recover deleted or inaccessible data from a damaged SQL database, consider using a professional SQL recovery tool.
