Database restore tests on hosted SQL Server and PostgreSQL
A backup job that reports success proves a file was written. Whether the database comes back depends on the log backups taken after that file, on the logins, jobs, keys and uploaded files kept outside the database, and on how long the restore takes on the server that would run it. Check the backups you run today, or the ones a host proposes, against the recovery targets each database needs.
Turning RPO and RTO into backup settings
The application owner sets a recovery point and a recovery time for each database. A reporting database rebuilt nightly from source tables can accept a day of loss. Checkout's orders database cannot, so the two get different schedules.
The recovery point sets the log backup interval. SQL Server restores to a point in time under the full recovery model with an unbroken chain of log backups. Bulk-logged allows it only for log backups that contain no minimally logged operations. Under the simple model, everything written since the last full or differential backup is unprotected. SELECT name, recovery_model_desc FROM sys.databases; shows which model each database runs, and the newest backup_finish_date with type = 'L' in msdb.dbo.backupset shows when each log was last backed up. For a 15-minute target, the log backup interval plus the time to copy each file off the database server has to stay under 15 minutes, because a log backup still sitting on the data volume goes down with it.
PostgreSQL archives WAL one completed segment at a time. On a quiet database a segment can stay open for hours, and every commit in it sits outside the archive until the segment fills, unless archive_timeout is set above zero. The PostgreSQL documentation calls a minute or so usually reasonable, and it points to streaming replication for a tighter target. Very short values bloat the archive, because a segment closed early is still written at full size. Watch SELECT last_archived_time, failed_count, last_failed_time FROM pg_stat_archiver; as the health check. If failed_count keeps climbing while the nightly base backup reports success, the recovery point keeps moving further back and the backup status still reads green.
A pricing bug found on day nine needs a backup chain that reaches back at least nine days, so retention has to cover how late a bad change gets noticed. On RDS, an instance created through the API or CLI without a retention setting keeps one day of automated backups, against seven for one created in the console, and 35 days is the maximum. RDS copies transaction logs to S3 every five minutes, and LatestRestorableTime in describe-db-instances shows how far the newest restorable point trails the primary.
Recovery time is the sum of every step between the failure and a user signing in: copying backup files to the restore server, the full restore, the differential and log restores or the WAL replay, a consistency check, reconnecting the application, and the owner's checks. Chain length drives the middle of that list. With a nightly full at 01:00, log backups every 15 minutes and no differentials, a failure at 23:50 means restoring the full and then 91 log backups in sequence, plus the tail of the log if the server survived. Add a differential every four hours and the same restore becomes the full, the 21:00 differential and 11 logs. PostgreSQL replays all WAL written since the base backup, so a database writing 40 GB of WAL a day with a weekly base backup can face up to 280 GB of replay before it opens. A daily base backup caps that near 40 GB.
What lives outside the application's database
A backup of one database holds its tables, stored code and database users. Restoring it to a different server also takes everything in this table, and any row left out can keep a restored copy from serving users.
| Item | Where it lives | What brings it back |
|---|---|---|
Settings in postgresql.conf, pg_hba.conf and pg_ident.conf (PostgreSQL) | Files on the server, edited by hand | A base backup copies them only when they sit in the data directory, which Debian and Ubuntu packages do not do. WAL replay never carries later edits, so keep them in configuration management |
| Roles and tablespaces (PostgreSQL) | The cluster, outside any single database | A physical base backup carries them. pg_dump of one database skips them, and pg_dumpall -g dumps them |
| Logins (SQL Server) | master | Logins scripted with their original SIDs, or ALTER USER ... WITH LOGIN on the restored copy |
| Agent jobs, alerts and Database Mail profiles (SQL Server) | msdb | A backup of msdb, or the jobs kept as scripts in source control |
| TDE certificate and private key (SQL Server) | master | BACKUP CERTIFICATE output stored apart from the database backups. Without it, an encrypted database will not restore on another server |
The TRUSTWORTHY setting (SQL Server) | A database property | A restore sets it to OFF. Set it back only where the application needs it |
| Uploaded documents and images | Object storage or a file share | Versioned storage rolled back to the same moment as the database |
| Connection strings, SMTP settings, API keys | Application configuration or a secret store | The secret store's own backup. A test restore gets test credentials |
SQL Server records when each certificate's private key was last backed up. A NULL pvt_key_last_backup_date in sys.certificates for the certificate protecting an encrypted database means it never has been, and that database can be restored only on the server holding the certificate today. On the restored copy, any database user whose SID matches nothing in sys.server_principals is orphaned, and the application cannot sign in as that user until ALTER USER app_user WITH LOGIN = app_user; maps it to a login again.
Rolling the database back to 10:40 while the file store stays at 14:00 leaves every upload after 10:40 with no row pointing at it, and rows from before 10:40 pointing at files someone has deleted since. Decide before the test which side wins, and have the owner name every store holding data the database refers to.
RESTORE VERIFYONLY, pg_verifybackup and DBCC CHECKDB
Ask which row of this table a host's backup report reflects. Each row proves more than the one above it.
| Evidence | What it proves | What it leaves open |
|---|---|---|
| The backup job reports success | The job ran and wrote a file | Whether the file is complete, readable, or the database you meant |
RESTORE VERIFYONLY | Every volume is readable and the backup set is complete | The structure of the data. Checksums are verified only if the backup was taken WITH CHECKSUM, which is off by default for uncompressed backups |
pg_verifybackup | Files match the base backup's manifest and checksums, and the WAL needed to recover it is present and parses | Checks that only a running server performs, so a test restore still matters |
A restore, then DBCC CHECKDB or pg_amcheck | The database opens, and its pages and indexes are consistent | Whether the application works against it |
| The owner's application checks | Users sign in and yesterday's orders appear | How long the next restore will take, unless someone wrote the timings down |
The first row can be wrong without anyone noticing. Petrichor keeps a person watching its automation because a job can keep reporting that it is running after it has stopped.
A 130-minute restore against a two-hour target
Take a hypothetical wholesale ordering platform, Pellham Supply, with a 220 GB SQL Server orders database, a 30-minute recovery point and a two-hour recovery time. Its test restore runs on a separate server, on a network that cannot reach production, and the timing log looks like this.
| Step | Minutes |
|---|---|
| Copy the 01:00 full, the 21:00 differential and 11 log backups to the restore server | 24 |
Restore the full WITH NORECOVERY | 38 |
Apply the differential and the logs, the last one WITH STOPAT and RECOVERY | 9 |
DBCC CHECKDB WITH NO_INFOMSGS | 27 |
| Map two orphaned users and recreate three Agent jobs from scripts | 12 |
| Start the application with outbound work disabled, then the owner's checks | 20 |
| Total | 130 |
The total misses the target by ten minutes, and the log shows where to find them. Copies of the backups kept on the restore side remove most of the first step, and logins scripted with their original SIDs shorten the fifth. Beyond that, the owner chooses between running DBCC CHECKDB WITH PHYSICAL_ONLY during an incident, with a full check once users are back, and moving the target to two and a half hours.
On RDS, aws rds restore-db-instance-to-point-in-time always creates a new instance. AWS notes that the new instance starts with the default parameter and option groups unless others are named, and that its volumes keep loading blocks from S3 after it reports available, so performance may lag until they finish. Time the owner's checks on that instance, because it is the one an incident would use.
Before the application starts, turn off everything that talks to the outside world: SQL Server Agent jobs that send mail, the Database Mail profile, the application's job workers, outgoing webhooks, and payment and messaging keys. A restored copy that still holds production SMTP settings and a running scheduler can send invoice emails to customers from a test server.
Record the backup files used, the recovery point requested and the one reached (the newest committed row the owner can find), the minutes for each step, and every manual fix. Rerun the test after each engine upgrade and on a schedule the owner sets. A result measured at 220 GB says little about the same database at 600 GB, or about recovering from a corrupted page instead of a dropped table.
Who signs off before a live database is overwritten
A request names the server or instance, the database, the target time with its time zone, and the name the restored copy will carry. In a ticket that says "restore orders to 14:15", the orders databases on the dev, QA and production servers are indistinguishable. PostgreSQL's recovery_target_time takes a numeric UTC offset or a full zone name. SQL Server's STOPAT takes a datetime, a type with no time zone, and msdb records backup times in the server's local time with the UTC offset in a separate column, so the ticket has to say which clock it means.
Restore beside the live database by default. For a dropped table or a bad data migration, restore under a new name such as orders_restore_0926 and copy the affected rows back, and every other write since the target time survives. On the same SQL Server instance, that restore needs WITH MOVE for each data and log file, or it tries to reuse the file paths the live database already holds. Rolling the live database back instead discards every write since the target time, across every table. SQL Server's REPLACE option deletes the existing database and skips the check that the tail of the log was backed up, so a request that authorizes it says so in words. PostgreSQL recovers to a point in time only for a whole cluster, which means recovering one table takes a cluster restore onto a separate server and pg_dump -t to pull the table out.
Make the application owner the approver for any restore that overwrites a live database, and have whoever runs it read the server, database and target time back before starting.
A managed hosting responsibility matrix splits the rest of the operating work between a host and an application team. On our cloud, the restore request is a ticket, filed the same way customers ask for database work and backups. A Petrichor engineer runs the restore, and the customer can call a named person at Petrichor about it.
Book a call with a consultant and bring the recovery targets for one database.