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. SIMPhony Production Databases 

1. IP Address: 10.234.1.1 

2. Host Name: FRAUSIMPH1V1002 

3. Operating System: Windows Server 2016 Standard 

4. Database Version: SQL Server 2016 Standard Edition 

5. Patch details: 2016 (SP2-GDR) (KB4532097) – 13.0.5102.14 (X64) 

2. SIMPhony UAT Databases 

1. IP Address: 10.234.1.3 

2. Host Name: FRAUSIMPH1V2002 

3. Operating System: Windows Server 2016 Standard 

4. Database Version: SQL Server 2016 Standard Edition 

5. Patch details: 2016 (SP2-GDR) (KB4532097) – 13.0.5102.14 (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 Files Replication  

For SIMPhony DB ( PRODUCTION ) server: 10.234.1.1 ( FRAUSIMPH1V1002 ) 

• We do backup for daily, weekly and monthly 

• We use full, incremental and synthetic full 

• Retention for the backup is 34 days on Disk 

• Retention for Tape is 10 years 

• It will be backup in FRSD and will replicate the backup in FRPA and stored in backup disk for 34 days 

Screenshot for the drives backup. 

Setup 2 : Database Backups 

We have our backup strategies for the Simphony DBs over our PRIMARY DB server i.e. 10.234.1.1. 

We are carrying FULL and t-log backups. 

1. FULL backup: 

Job: FullBackupPlan.Subplan_1 

Job frequency: Daily at 4 AM 

This job takes the FULL backup daily at 4 AM at the below location over 10.234.1.1 

S:\SIMphonyBackup\Full\ 

Job history: 

LOG backup : 

              Job: complete backup plan.log backups 

 Job frequency: Every 3 hours 

            This job takes the log backup every 3 hours at the below location over 10.234.1.1 server: 

             S:\SIMphonyBackup\Transaction\ 

             Job History: 

List of Database backup Copies 

FULL backup files: 

T-log backup files: 

BackUp Frequency 

FULL backup:  Everyday at 4 AM 

T-log backup: Every 3 hours 

Database recovery Steps from File System 

As SIMPhony PROD server 10.234.1.1 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. 

2^nd 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 Simphony DBs over 10.234.1.1 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.234.1.1
2. Select required backup files from the browse button (latest FULL backup + all t-log backup files post last FULL backup)  
                   
   Select Options->WITH REPLACE->Recovery state as WITH RECOVERY->Click OK 
    
    
   T-SQL method: 
   SCLPM or any other DB can be restored by running the following T-SQL: 
   USE [master] 
   RESTORE DATABASE [SCLM] FROM  DISK = N’  
   S:\SIMphonyBackup\Full\recentFULLbackupfile.bak’ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5 
   RESTORE LOG [SCLM] FROM  DISK = N’ S:\SIMphonyBackup\Transaction\firstlogile.trn’ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5  
   . 
   . 
   . 
   RESTORE LOG [SCLPM] FROM  DISK = N’ S:\SIMphonyBackup\Transaction\\lastlogfile.trn’ WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5  
    
   GO 
   Use ‘WITH MOVE’ in case of the change in directory for SCLPM or other DB files.