Home » Blog » SQL Server » RTO and RPO in SQL Server

RTO and RPO in SQL Server

Neeraj Kumar
Published By Neeraj Kumar
Debasish Pramanik
Approved By Debasish Pramanik
Published On
Last Updated

RTO and RPO in SQL Server set the recovery goals of your database. RTO states how long a database can be offline after an incident and RPO states how much data you can lose. Backup frequency sets RPO and restore speed sets RTO. Three methods measure and meet both numbers. Msdb shows your real backup gap and a test restore shows your real restore time. Matching schedules close the gaps. Recovery software restores data when the only backup is damaged.

Your manager asks for RTO and RPO numbers for DispatchDB and nobody can say what they are today. Both numbers need real measurements before you promise anything to the business.

Search results define the terms and list tier tables with no source. Few show how to measure your current RTO and RPO in SQL Server.

This guide explains how to set recovery goals with a measuring query, a schedule and a rescue method, plus the gap that fools most RPO claims.

As per Microsoft's business continuity, RTO is the time an application needs to fully recover after an unplanned event and RPO is the amount of data loss you can tolerate.

What are RTO and RPO in SQL Server?

Both terms describe a business promise. Backups and restores decide whether you keep it.

TermQuestion It AnswersWhat Sets It
RTOHow long can the database be offline?Restore speed
RPOHow much data can you lose?Backup frequency

Microsoft states in the backup and restore overview that a good strategy balances data availability and data loss against the cost of backups. Business needs decide the numbers and not the database.

Microsoft lists typical Azure SQL Database values in its overview. Zone redundancy gives an RPO of 0 while geo restore takes minutes or hours for both numbers.

3 Methods to Meet Your RTO and RPO in SQL Server

No purchase is needed for Methods #1 and #2. Method #3 starts from a damaged backup.

FeatureMeasureMatch the ScheduleRecovery Software
PriceIncluded in SQL ServerIncluded in SQL ServerScan before purchase and export after
Best forFinding real numbersMeeting new goalsDamaged BAK files
NeedsQuery rights on msdbBackup permissionWindows PC and a BAK copy
Main limitNeeds a test serverCosts storage spaceTrial export limit

Method #1: Measure Your Real RPO and RTO (Free, Needs Query Rights)

Your real RPO is the gap between the last backup and now. Msdb stores a row for every backup set. Microsoft describes the table in backupset.

SELECT type, MAX(backup_finish_date) AS last_finish,
       DATEDIFF(MINUTE, MAX(backup_finish_date), GETDATE()) AS minutes_old
FROM msdb.dbo.backupset
WHERE database_name = N'DispatchDB' AND is_copy_only = 0
GROUP BY type;

Type D is a full backup and I is a differential backup and L is a log backup per Microsoft. Compare the smallest minutes_old value with your RPO.

Your real RTO is the time a full restore takes. Time a test restore on a test server and never on production.

DECLARE @start datetime2 = SYSDATETIME();
RESTORE DATABASE DispatchDB_Test
FROM DISK = N'E:\Backups\DispatchDB_Full.bak'
WITH MOVE N'DispatchDB' TO N'F:\Test\DispatchDB_Test.mdf',
     MOVE N'DispatchDB_log' TO N'F:\Test\DispatchDB_Test.ldf',
     RECOVERY;
SELECT DATEDIFF(SECOND, @start, SYSDATETIME()) AS restore_seconds;

Add the log backups you would restore in a real incident to that time.

Microsoft states that you have no backup strategy until you restore backups successfully and test the restored database for physical consistency.

Method #2: Match the Backup Schedule to Your Goals (Free, Needs Backup Permission)

Select the plan that fits the number you need to improve.

GoalBackup PlanMicrosoft Note
Lower RPOFrequent log backupsFrequent log backups work in the full model
Lower RTODifferential backups between full backupsThey reduce the log backups you restore
BothWeekly full and daily differential and frequent log backupsAn example schedule in the log backup guide

Frequent log backups need the full model.

ALTER DATABASE DispatchDB SET RECOVERY FULL;
GO
BACKUP LOG DispatchDB
TO DISK = N'E:\Backups\DispatchDB_Log_0900.trn'
WITH CHECKSUM;

As per Microsoft's transaction log backup, a log backup every 15 to 30 minutes can be enough for many plans. Store backups on separate hardware from the database files as Microsoft advises.

Method #3: Rescue a Damaged Backup With EmailDoctor (Free Scan, Paid Export)

Your RTO fails when the only backup is damaged. EmailDoctor SQL Backup Recovery opens a copy of the BAK file and lists its tables without a server. Trial exports stop at 25 records in all tables.

Work on a copy and never the original file.

Add the Damaged Backup

  1. Open the recovery software from its desktop shortcut. Software home screen
  2. Click Open and choose Browse to find the DispatchDB_Full.bak copy. Browse button for the BAK file BAK file selection window
  3. Confirm the Auto detect SQL server file (.bak) version option. Version detection option
  4. Switch to Multiple Backup File Options and add every file when the backup is split. Multiple backup file options Add file and add folder buttons
  5. Start the scan with Recover. Recover button Scanning progress
  6. Open any table to preview its rows. Preview of recovered data

Export to SQL Server

  1. Press Export and mark SQL Server Database as the output format. Export options
  2. Enter the server details and an Authentication mode. Authentication mode options
  3. Select Create New Database and name it DispatchDB_Rescue. Create New Database option
  4. Accept the default collation. Collation setting
  5. Tick the tables you need and select With Schema & Data before you press Export. Objects marked for export Schema and data option
  6. Refresh Databases in SSMS to see DispatchDB_Rescue. Recovered database in SSMS

Which Problems Break RTO and RPO in SQL Server?

Five problems break most promises:

ProblemReasonFix
Log backups never existThe database is not in the full modelSet it to full and schedule log backups
Last backup is oldThe schedule stoppedCheck minutes_old in Method #1
Restore takes too longA long chain of log backupsAdd differential backups
Backup file is unusableNobody tested itRestore it on a test system and execute DBCC CHECKDB
Backup and data share one diskOne failure removes bothStore backups on a separate device

Switch to Method #3 on a copy when every backup fails.

Frequently Asked Questions

Is RTO the Time You Need to Recover?

Yes. Microsoft defines RTO as the time an application needs to fully recover after an unplanned event.

Is RPO Your Limit for Data Loss?

Yes. Microsoft defines RPO as the amount of data loss you can tolerate after an unplanned event.

Does Backup Frequency Set RPO?

Yes. Microsoft warns that the risk of data loss grows as backups age.

Can Differential Backups Shorten Restore Time?

Yes. Per Microsoft they reduce the number of log backups you restore.

Do Log Backups Work in the Full Model?

Yes. Per Microsoft the full model and the bulk logged model both use log backups.

Does Zone Redundancy Give an RPO of 0 in Azure SQL Database?

Yes. Microsoft lists an RPO of 0 for zone redundancy in its overview.

Must You Test Backups Before You Promise RTO and RPO?

Yes. Microsoft states that testing is required and that a restored copy needs DBCC CHECKDB.

Does Msdb Store Backup History?

Yes. Microsoft states that msdb stores a row for every backup set.

Can Recovery Software Open a Damaged Backup?

Yes. Recovery software scans the BAK file without SQL Server and saves its tables in a new database.

Does the Trial Export Every Record?

No. Trial exports stop at 25 records in all tables. A license ends the limit.

Author’s Verdict

RTO and RPO are business promises that your backups must back. Measure your real numbers before you set new ones.

Match the schedule to the gap you find and test one restore every quarter. Recovery software saves data when a backup is damaged.

Record both numbers and share them with your manager.

  • RTO and RPO in SQL Server describe recovery time and data loss.
  • Msdb shows your real backup gap and a test restore shows your real restore time.
  • Frequent log backups lower RPO and differential backups lower RTO.
  • Backups on a separate device and a tested restore protect both numbers.
  • Recovery software saves the readable tables of a corrupt BAK file in a new database.
  • Verify every step on a duplicate first.
  Neeraj Kumar

By Neeraj Kumar

Neeraj Kumar is a Principal Technical Architect at EmailDoctor with 19+ years of experience in email migration, cloud migration, data recovery and digital forensics. He guides enterprise teams through Exchange, Office 365 and Google Workspace projects and shares what works as an EmailDoctor writer. Every method in his posts is tested on Windows and Mac before it goes live.