Unless you perform regular restores of your database backups, you don’t know that you actually have a valid backup. In a career spanning over 30 years, I’ve seen two occasions where a company was performing backups (or so they thought!) and sending tapes offsite, assuming they were good when in fact the tapes were blank!
The majority of SQL Server installations use Ola Hallengren’s maintenance solution (and certainly all the ones I’ve had anything to do with).
If you are doing regular (5 minutes or less) transaction log backups, a restore might involve applying quite a few transaction logs.
I’ve written a short LINQPad script here which will generate the TSQL to perform a database restore either from a point in time or the latest available, based upon the default locations and naming conventions used by Ola’s backups. It’s Differential backup aware, as well as creating the multiple Transaction Log restore statements. It’s also takes into account where backups are split into separate backup files (which is quite common). You specify the server name, the database name, the root folder where the backups are stored, and either a point in time or the latest.
Disclaimer: Use at your own risk AND test thoroughly!
Example output:
USE [master]
RESTORE DATABASE [AdventureWorks] FROM
DISK = N'C:\temp\Backup\K7\AdventureWorks\FULL\K7_AdventureWorks_FULL_20211118_151558.bak'
WITH NORECOVERY, REPLACE
RESTORE DATABASE [AdventureWorks] FROM
DISK = N'C:\temp\Backup\K7\AdventureWorks\DIFF\K7_AdventureWorks_DIFF_20211118_152101.bak'
WITH NORECOVERY
RESTORE DATABASE [AdventureWorks] FROM
DISK = N'C:\temp\Backup\K7\AdventureWorks\LOG\K7_AdventureWorks_LOG_20211118_152226.trn'
WITH NORECOVERY, STOPAT = '2021-11-21 17:07:22'
RESTORE DATABASE [AdventureWorks] WITH RECOVERY