MS SQL How to find the latest restore info

4 min read

So in my work I do automatically downloading and restore a database, however sometimes the automation doesn’t work properly perhaps due internet connection problem or space full, etc. In order to check that restore has been preformed successfully I just need to check if today is the last restore date.

So here’s my query, using below query you can get information of when, duration of restore and what backup filename used to restore the database. Please note on line 10 you are supposed to put your database name.

SELECT TOP 1 [rs].[destination_database_name] ,
[rs].[restore_date] ,
[bs].[backup_start_date] ,
[bs].[backup_finish_date] ,
[bs].[database_name] AS [source_database_name] ,
[bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE rs.destination_database_name LIKE 'DATABASE_NAME_HERE'
ORDER BY [rs].[restore_date] DESC

How to implement in batch file to compare with today’s date? Here’s what I do. Don’t forget to change the connection ID on line 7-10.

@echo off
rem Created at 5:51 PM 29-May-15 by David B to check if database DB_NAME is current
rem DO NOT MODIFY UNLESS YOU KNOW WHAT YOU ARE DOING !!

rem -=-=-=-=-=-=-=-=-=- SQL Connection =-=-=-=-=-=-=-=-=-=-=

set "sqlConnection=SERVER_NAME\INSTANCE_NAME"
set "sqlUser=DATABASE_USER"
set "sqlPassword=DATABASE_PASSWORD"
set "sqlDB=DATABASE_NAME"

rem -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

set "RegKey=HKCU\Control Panel\International"
rem echo.
rem echo.Copy current format to temporary
reg copy "%RegKey%" "%RegKey%-Temp" /f>nul
rem echo.
rem echo.Modify date format
reg add "HKCU\Control Panel\International" /v sShortDate /d "dd/MM/yyy" /f>nul
@REM reg query "HKCU\Control Panel\International" /v sShortDate
set LogDate=%date%

set yyyy=

set $tok=1-3
for /f "tokens=1 delims=.:/-, " %%u in ('date /t') do set $d1=%%u
if "%$d1:~0,1%" GTR "9" set $tok=2-4
for /f "tokens=%$tok% delims=.:/-, " %%u in ('date /t') do (
for /f "skip=1 tokens=2-4 delims=/-,()." %%x in ('echo.^|date') do (
set %%x=%%u
set %%y=%%v
set %%z=%%w
set $d1=
set $tok=))

if "%yyyy%"=="" set yyyy=%yy%
if /I %yyyy% LSS 100 set /A yyyy=2000 + 1%yyyy% - 100

set todDate=%yyyy%-%mm%-%dd%

rem Restore date old format configuration
reg copy "%RegKey%-Temp" "%RegKey%" /f>nul
reg delete "%RegKey%-Temp" /f>nul

for /f "delims= " %%i in (
'SQLCMD -S %sqlConnection% -d %sqlDB% -U %sqlUser% -P %sqlPassword% -Q "SET NOCOUNT ON;SELECT TOP 1 bmf.physical_device_name FROM msdb..restorehistory rs INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id] INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] WHERE rs.destination_database_name LIKE '%sqlDB%' ORDER BY [rs].[restore_date] DESC" -h -1 -s "," '
) do set fileLoc=%%i

for /f "delims=" %%i in (
'SQLCMD -S %sqlConnection% -d %sqlDB% -U %sqlUser% -P %sqlPassword% -Q "SET NOCOUNT ON;SELECT TOP 1 [rs].[restore_date] FROM msdb..restorehistory rs INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id] INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] WHERE rs.destination_database_name LIKE '%sqlDB%' ORDER BY [rs].[restore_date] DESC" -h -1 -s "," '
) do set resDate=%%i

for /f "tokens=1-4 delims=- " %%d in ("%resDate%") do (
set year=%%d
set month=%%e
set day=%%f
)
if %month% == 01 (
set monthdesc=Jan
) else if %month% == 02 (
set monthdesc=Feb
) else if %month% == 03 (
set monthdesc=Mar
) else if %month% == 04 (
set monthdesc=Apr
) else if %month% == 05 (
set monthdesc=May
) else if %month% == 06 (
set monthdesc=Jun
) else if %month% == 07 (
set monthdesc=Jul
) else if %month% == 08 (
set monthdesc=Aug
) else if %month% == 09 (
set monthdesc=Sep
) else if %month% == 10 (
set monthdesc=Oct
) else if %month% == 11 (
set monthdesc=Nov
) else if %month% == 12 (
set monthdesc=Dec
)
set myDate=%day%-%monthdesc%-%year%

for /f "tokens=1-4 delims=: " %%d in ("%resDate%") do (
set hour=%%e
set minute=%%f
set second=%%g
)
set myTime=%hour%:%minute%

echo.
echo %resDate% | findstr %todDate% >nul && (
echo Database '%sqlDB%' is FRESH, restored today at %myTime%
echo Restored from '%fileLoc%'
) || (
echo Database '%sqlDB%' is OLD, last restored on %myDate% at %myTime%
echo Restored from '%fileLoc%'
echo.
echo Please check if backup exist or manually restore
)
pause>nul

Feel free to use the whole code or portion to your liking and purpose. Let me know if you have question and put it in comment section below.