Restore Latest Backup in SQL Server

Many times we face scenario where we have to restore database with recent backup. And we know no one likes to do it manually every time. So let automate same.

Few things required are

1. Configure your backup to happen in specific disk every time
2. Make sure your backup restore works on same version or higher to low version
3. Make sure backup file if belong to some tool then restore server must have same tool installed
4. You cant restore compressed backup on standard edition


DECLARE @cmd varchar(100)
DECLARE @path varchar(100)
Declare @backup_file varchar (200)

SET @Path = 'c:\Windows\' --: Pass the path in which you want to search
SET @cmd = 'dir ' + @path + ' /A /OS /-C'

CREATE Table #MyTable (Results varchar(500))
INSERT INTO #MyTAble
EXEC XP_CMDSHELL @cmd
SELECT LEFT(Results, 20) [create_date],
CASE LTRIM(RTRIM(SUBSTRING(Results, 21, 18))) WHEN ''
THEN 1 ELSE 0 END as is_directory, CASE LTRIM(RTRIM(SUBSTRING(Results, 21, 18))) WHEN ''
THEN NULL ELSE CAST(LTRIM(RTRIM(SUBSTRING(Results, 21, 18))) AS BIGINT) END as size_in_bytes, SUBSTRING(Results, 40, Len(Results)) AS [name]
into #Mytable1
FROM #MyTable
WHERE ISNULL(PATINDEX('%__/__/____%', Results), 0) != 0

Select Top 1 @backup_file = name from #Mytable1 order by create_date

DROP TABLE #MyTable
DROP TABLE #Mytable1
Select @path = @path + @backup_file

RESTORE DATABASE [database]
FROM DISK = @path
WITH REPLACE, STATS = 10
GO