**** NOTICE!!!! Please see Scott's warning at https://www.sqlservercentral.com/forums/topic/restore-autmomation#post-3979292 on this subject below and my post right after his. I only use this method on "test" boxes and didn't stress what could happen if the once in a lifetime failure occurs between the SINGLE_USER and the MULTI-USER commands that I use in the code below. You should start with the OFFLINE/ONLINE/DROP method that Scott has posted first and resort to the SINGLE/MULTI user method only on a test/Dev system and only if you experience the issues that I had, which are rare in most environments.
Here's the "dity" I use on one of my test boxes. Read the "TODO" stuff in the comments at the top for what you'll need to do to make it work for you.
Also note that I'm rather rigorous about keep logical names and file names in the particular format/naming that's in the code. You may need to do a little work or make it so you read the header from the BAK file you find (this code always finds the latest one and doesn't check to see if it's in the process of being backed up) to get the correct file info. Of course, that will require a bit of dynamic SQL.
You've been working with SQL for quite a while. You should have already had something similar to do occasional restores to make sure that your databases actually will restore. I automatically restore several databases with some more complete code than this but figure that I don't know your environment and you have to have some of the fun. 😀 If you make money doing this, send me some. 😀
-- Todo... Look for everything that contains "DbNameToRestore" and change the name there.
-- Todo... Look for "todo" for other things you may need to change.
--===== Make sure that we're NOT in the database that we want to restore.
--===== Local Variables
-- /s = Include SubDirectories. There shouldn't be any but it also enables FULL PATH for the current directory.
-- /b = Bare-bones mode where only the path (FULL PATH in this case) is returned.
-- /o = "Order by"
-- -d = Descending order by modified date (default)
DECLARE @DirCmd VARCHAR(500) = 'DIR "\\MachineNameHere\PathToBakFiles\*.bak" /s /b /o-d' --TODO... changes required here
,@DbName SYSNAME = 'DbNameToRestore'
--===== Create the table to store the results from the @DirCmd execution.
DROP TABLE IF EXISTS #DirResult;
CREATE TABLE #DirResult
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
-- Get the latest backup file to restore
--===== Execute the DIR command to get the list of the *.bak files in descending order by date.
-- This list contains the FULL PATH UNC for each of the files.
-- The latest file will have a RowNum = 1.
INSERT INTO #DirResult WITH (TABLOCK)
EXEC xp_CmdShell @DirCmd
--===== Get the latest FULL PATH UNC for the file we want to restore.
-- Because of the order of the result from the @DirCmd, the latest file is always the
-- first row in the #DirResult table.
SELECT @RestoreFileName = RestoreFileName
WHERE RowNum = 1
RAISERROR('FILE FOUND...',0,0) WITH NOWAIT;
SELECT RestoreFileName FROM #DirResult WHERE RowNum = 1;
-- Do the unconditional drop/restore
--===== If the database exists, boot everyone and everything out and drop the database.
RAISERROR('Working on DROP...',0,0) WITH NOWAIT;
IF DB_ID(@DbName) IS NOT NULL
BEGIN --This won't work if the DB is in a restoring state. I don't have that problem.
ALTER DATABASE [DBNameToRestoreHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [DBNameToRestoreHere] SET MULTI_USER;
DROP DATABASE [DBNameToRestoreHere];
--===== Do the restore with MOVEs of the files to the correct drives.
RAISERROR('Working on RESTORE...',0,0) WITH NOWAIT;
RESTORE DATABASE [DBNameToRestoreHere]
FROM DISK = @RestoreFileName
WITH FILE = 1
,MOVE N'DbNameToRestore' TO N'V:\SQLData\DbNameToRestore.mdf' --todo possible changes for logical names and paths
,MOVE N'DbNameToRestore_Log' TO N'W:\SQLLOG\DbNameToRestore.ldf'
,NOUNLOAD, REPLACE, STATS = 1
,BUFFERCOUNT=17, MAXTRANSFERSIZE=1048576; --todo these settings work well on my machine. YMMV.
--===== Rename the LOGICAL FILE NAMES (uncomment if needed)
--ALTER DATABASE [DBNameToRestoreHere] MODIFY FILE (NAME=N'DbNameToRestore' , NEWNAME=N'DbNameToRestore')
--ALTER DATABASE [DBNameToRestoreHere] MODIFY FILE (NAME=N'DbNameToRestore_Log', NEWNAME=N'DbNameToRestore_Log')
--===== Set the database to the normal configuration for this server --todo change these settings as required
ALTER DATABASE [DBNameToRestoreHere] SET RECOVERY SIMPLE WITH NO_WAIT;
ALTER AUTHORIZATION ON DATABASE::[DBNameToRestoreHere] TO [SA]
ALTER DATABASE [DBNameToRestoreHere] SET TRUSTWORTHY ON;