What is the backup strategy you uses for point in time recovery in SQL Server if the database corrupted?

Lets say my  backup strategy is:

1. Full - Weekly backup in non-business hours (At Sunday at 12:00 AM)
2. Differential - Daily once in non-business hours. (At Daily at 12:00 AM)
3. Transactional Log - Every 15 minutes. And the last backup was done at Apr 05, 2025 12:00 AM

Situation: Employee_DB corrupted at Apr 05, 2025 12:10 AM and database is not accessible to and table.

Point in time recovery solution:

1st of all, I will check the current database mode and will try to bring online the corrupted data base.

SELECT DATABASEPROPERTYEX ('Employee_DB', 'STATUS')

Lets understand the database state or mode:

1. ONLINE - Online state means the database is running fine. No issue with database state.

2. SUSPECT - SUSPECT states means that the database is not available for user access. In this database state, the database recovery process has started but not completed successfully. database went in suspect mode due to DB file corruption, unavailable database files or improper shutdown of the SQL database server while running a big transaction. Run one by one below commands.

  EXEC sp_resetstatus Employee_DB;

  ALTER DATABASE Employee_DB SET EMERGENCY

  DBCC checkdb(Employee_DB)

  ALTER DATABASE Employee_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  DBCC CheckDB (Employee_DB, REPAIR_ALLOW_DATA_LOSS)

  ALTER DATABASE Employee_DB SET MULTI_USER

3. RESTORING - This is not an issue, if you restore the DB with NORECOVERY option and at last you need to run below query. It will change the DB state to online.

  RESTORE DATABASE [Employee_DB] WITH RECOVERY

4. RECOVERING - To fix this issue, you should perform a transaction log backup on your database, shrink the transaction log as much as possible and finally specify an initial size of the transaction log file large enough to handle the database workload

  BACKUP LOG Employee_DB TO 'E:\backup\Employee_T-log_backup.trn'

5. RECOVERY PENDING- This issue means the .mdf file or .ndf file is missing and SQL server is unable to open this file. To fix this issue locate the missing file again, taking the database offline and brining it online.

Check the error log. Mostly this issue resolved automatically once SQL server able to open the .mdf and .ndf files.

6. OFFLINE - To fix this issue, run below query

  ALTER DATABASE [Employee_DB] SET ONLINE;


7. EMERGENCY - If your DB went in this state. run below commands.

  DBCC checkdb (Employee_DB)

  ALTER DATABASE Employee_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 
  DBCC CheckDB (Employee_DB, REPAIR_ALLOW_DATA_LOSS)

  ALTER DATABASE Employee_DB SET MULTI_USER


If above solution not working than try to take Tail log backup:

  BACKUP LOG Employee_DB TO 'E:\Backup\Tail-Log\Employee_DB3.trn'

  WITH NORECOVERY; NO_TRUNCATE;


Solution: Point in Time Recovery Steps by Restoring database backups:

A. Restore latest full backup of the database with NO RECOVERY option

   RESTORE DATABASE Employee_DB FROM 'E:\Backup\Full\Employee_DB.bak'

   WITH FILE=3 NORECOVERY;

B. Now restore latest differential back up with NO RECOVERY option

  RESTORE LOG Employee_DB FROM 'E:\Backup\Diff\Employee_DB.trn'

  WITH FILE=4, NORECOVERY,

C. Now restore all the T-logs backup with NO RECOVERY option.

  RESTORE LOG Employee_DB FROM 'E:\Backup\T-Logs\Employee_DB_1.trn'

  WITH FILE=5, NORECOVERY,


  RESTORE LOG Employee_DB FROM 'E:\Backup\T-Logs\Employee_DB_2.trn'

  WITH FILE=6, NORECOVERY,


D. Restore Tail log back up with RECOVERY OPTION.

  RESTORE LOG Employee_DB

  FROM 'E:\Backup\Tail-Log\Employee_DB_3.trn'

  WITH FILE=7, NORECOVERY, STOPAT = 'Apr 05, 2025 12:09 AM';


E. Finally bring the database state from NORECOVERY to RECOVERY using below command.

  RESTORE DATABASE Employee_DB 

  WITH RECOVERY;

You are done!