eSCLator Database Disaster Recovery Manual

Updated on December 9, 2025

Introduction 

Definition 

Database recovery refers to the process of restoring a database to a correct and consistent state after a failure or corruption. It involves recovering data from backups or using transaction logs to undo and redo changes made to the database. 

Scope 

The scope of a database recovery process encompasses all actions required to restore a database to a consistent state after a failure or error. This includes identifying the extent of the failure, determining the recovery options, and executing the recovery procedures. The recovery process can involve using backups, transaction logs, and other recovery techniques to bring the database back to a functional state 

Following Databases comes under the scope of this process 

  1. HR System Production Databases 
  1. IP Address: 10.132.196.55 
  1. Host Name: INNOPOR1V0002 
  1. Operating System: Windows Server 2019 Standard 
  1. Database Version: SQL Server 2019 Enterprise Edition 
  1. Patch details: 2019 (RTM) – 15.0.2000.5 (X64) 
  1. HR System UAT Databases 
  1. IP Address: 10.132.196.57 
  1. Host Name: INNOTSTDB1V0001 
  1. Operating System: Windows Server 2019 Standard 
  1. Database Version: SQL Server 2019 Enterprise Edition 
  1. Patch details: 2019 (RTM) – 15.0.2000.5 (X64) 

Conventions 

Database recovery processes involve techniques like backup and restore, transaction logging, and checkpointing to ensure data consistency and integrity after failures or errors. These processes are crucial for maintaining data reliability and availability, allowing for quick recovery and minimal data loss.  

The key words “must”, “must not”, “required”, “shall”, “shall not”, “should”, “should not”, recommended”, “may”, and “optional” in this document are to be interpreted as described in RFC2119.” 

Setup 1 : Database Replication using Log Shipping 

Purpose 

SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations 

Operating Mode 

We are using Restore mode – i.e. the target database where Log shipping is being done is not accessible for querying. 

Frequency: 15 Minutes 

There will be data loss of 15 minutes in case of an incident. 

Servers involved: 

Purpose Environment IP Host Name DB Version 
DB Server Production   10.132.196.55 INNOPOR1V0002 SQL Server 2019 Enterprise Edition 
DB Server UAT  10.132.196.57 INNOTSTDB1V0001 SQL Server 2019 Enterprise Edition 

Log Shipping works on three steps:  

Step 1 : Job to take Production Database Backup  

  1. Configured on 10.132.196.55- Production Primary Server 
  1. Job name :LSBackup_SCL 
  1. Backup Folder: \\ INNOPOR1V0002\E:\backup shared folder SCL 
  1. Database Name: SCL 

Step 2 : Job to Copy T-Log Backups from Production Primary Server to UAT Server (configured on 10.132.196.57) 

  1. Configured on 10.132.196.57 (UAT Server) 
  1. Job name : LSCopy_INNOPOR1V0002_SCL 
  1. Copy Folder : \\ INNOTSTDB1V0001\I:\backup copy folder SCL 

Step 3:Job to restore the copied T-log backups to the secondary DB i.e. SCL_LS 

    h. Configured on 10.132.196.57 (UAT Server) 

     i. Job name: LSRestore_INNOPOR1V0002_SCL 

             j. Database name: SCL_LS 

Monitoring requirements to cover limitations coming with this configuration 

In case of failure of UAT server (10.132.196.57) as we are not expecting the UAT server will be providing 100% availability.  

IT team will be informing the DBA before having such UAT downtime, DBA will be disabling these 3 Log Shipping jobs and will enable them post downtime. 

Setup 2 : Database Files Replication  

Below is the backup schedule of database server drives: 

  1. We have a daily evening backup also at 11PM. 
  1. We are taking the drives backup to Backup server HDD ( Repository ) 
  1. We are taking the monthly drives backup to Tape drive. 

Setup 3 : Database Backups 

We have our backup strategies for the SCL and other DBs over our PRIMARY DB server i.e. 10.132.196.55. 

We are carrying FULL and t-log backups. 

  1. FULL backup: 

Job: complete backup plan.full backup plan 

Job frequency: Daily at 2 AM 

This job takes the FULL backup daily at 2 AM at the below location over 10.132.196.55 server: 

F:\Program Files\Microsoft SQL Server\MSSQL15.INNOPOR1V0002\MSSQL\Backup 

LOG backup : 

Job: complete backup plan.log backups 

Job frequency: Every 2 hours 

This job takes the log backup every 2 hours at the below location over 10.132.196.55 server: 

F:\Program Files\Microsoft SQL Server\MSSQL15.INNOPOR1V0002\MSSQL\Backup 

List of Database backup Copies 

FULL backup files: 

T-log backup files: 

BackUp Frequency 

FULL backup:  Everyday at 2 AM 

T-log backup: Every 15 minutes 

LOG SHIPPING IMPLEMENTATION Specifications 

Setting Up SQL Server Log Shipping 

Prerequisites 

  • SQL Server Standard/Enterprise Edition on both servers. 
  • Both servers must be accessible over the network. 
  • Shared folder accessible by both servers. 
  • SQL Server Agent must be running on both servers. 

Step 1: Configure Log Shipping (Primary Server) 

  1. In SQL Server Management Studio (SSMS), right-click on the primary database > Tasks > Ship Transaction Logs. 
  1. Enable this as a primary database in a log shipping configuration. 

Configure Backup Settings: 

  • Set backup retention and frequency. 
  • Configure SQL Server Agent schedule. 

Step 2: Add Secondary Server 

  1. In the same wizard, click Add under secondary server. 
  1. Connect to the secondary instance. 

Initialize Secondary Database from any of the three options. 

Step 3: Configure Copy Job 

  • Configure retention and schedule. 

Step 4: Configure Restore Job 

  • Choose NORECOVERY or STANDBY mode. 
  • Configure delay (optional) and restore frequency. 

Notes 

  • Use sp_help_log_shipping_monitor to monitor status. 
  • Keep an eye on: 
  • Last backup time 
  • Last copy time 
  • Last restore time 

Database Recovery from Log Shipping 

We can utilize our Log Shipping configuration to recover our SCL database 

Step1: Disable the Backup job over the eSCL PROD server ( 10.132.196.55 ). Un-check the ‘enabled’ button. 

Step 2: Disable the Copy job over the eSCL UATserver ( 10.132.196.57 ). Un-check the ‘enabled’ button. 

Step 3: Disable the Restore job over the eSCL UATserver (10.132.196.57 ). Un-check the ‘enabled’ button.  

Step 4: 

  1. Connect 10.132.196.57 via SSMS. 
  1. Connect master DB. 
  1. Run below script: 

RESTORE DATABASE SCL_LS WITH RECOVERY; 

It will recover SCL DB,the lag time from the PROD SIMPra DB will be the time post the last restore backup done ( maximum 15 mins ). 

Database recovery Steps from File System 

As SCL PROD server 10.132.196.67 drives backups are taken every evening and monthly. 

There are two ways from where we can get back our drives data: 

1st method: We will be utilizing backups from the Backup server HDD (repository) to recover in case of any failure. 

2nd Method: Monthly backups are taken to Tape drive, it can be utilized too. 

Database Recovery by restoring from Backup 

We will be utilizing these FULL/T-log backups to recover SCL or other DB over 10.132.196.55 server in the case of disaster/failures. 

We will carry out our restoration process in the following ways: 

SSMS GUI 

  1. Right click Databases->Select Restore Database post connecting 10.132.196.55 
  1. Select required backup files from the browse button (latest FULL backup + all t-log backup files post last FULL backup)  
  1. Select Options->WITH REPLACE->Recovery state as WITH RECOVERY->Click OK 

T-SQL method: 

SCL DB can be restored by running the following T-SQL: 

USE [master] 

RESTORE DATABASE [SCL] FROM  DISK = N’ F:\Program Files\Microsoft SQL Server\MSSQL15.INNOPOR1V0002\MSSQL\Backup\SCL\recentFULLbackupfile.bak’ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5 

RESTORE LOG [SCL] FROM  DISK = N’ F:\Program Files\Microsoft SQL Server\MSSQL15.INNOPOR1V0002\MSSQL\Backup\SCL\firstlogile.trn’ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5  

. 

. 

. 

RESTORE LOG [SCL] FROM  DISK = N’ F:\Program Files\MicrosoftSQLServer\MSSQL15.INNOPOR1V0002\MSSQL\Backup\lastlogfile.trn’ WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5  

GO 

–Use ‘WITH MOVE’ in case of the change in directory for SCL or other DB files. 

END OF DOCUMENT 

Leave a Reply

Your email address will not be published. Required fields are marked *