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.<\/p>\n
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.<\/p>\n
SELECT TOP 1 [rs].[destination_database_name] ,\r\n[rs].[restore_date] ,\r\n[bs].[backup_start_date] ,\r\n[bs].[backup_finish_date] ,\r\n[bs].[database_name] AS [source_database_name] ,\r\n[bmf].[physical_device_name] AS [backup_file_used_for_restore]\r\nFROM msdb..restorehistory rs\r\nINNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]\r\nINNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]\r\nWHERE rs.destination_database_name LIKE 'DATABASE_NAME_HERE'\r\nORDER BY [rs].[restore_date] DESC<\/pre>\nHow 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.<\/p>\n
@echo off\r\nrem Created at 5:51 PM 29-May-15 by David B to check if database DB_NAME is current\r\nrem DO NOT MODIFY UNLESS YOU KNOW WHAT YOU ARE DOING !!\r\n\r\nrem -=-=-=-=-=-=-=-=-=- SQL Connection =-=-=-=-=-=-=-=-=-=-=\r\n\r\nset "sqlConnection=SERVER_NAME\\INSTANCE_NAME"\r\nset "sqlUser=DATABASE_USER"\r\nset "sqlPassword=DATABASE_PASSWORD"\r\nset "sqlDB=DATABASE_NAME"\r\n\r\nrem -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=\r\n\r\nset "RegKey=HKCU\\Control Panel\\International"\r\nrem echo.\r\nrem echo.Copy current format to temporary\r\nreg copy "%RegKey%" "%RegKey%-Temp" \/f>nul\r\nrem echo.\r\nrem echo.Modify date format\r\nreg add "HKCU\\Control Panel\\International" \/v sShortDate \/d "dd\/MM\/yyy" \/f>nul\r\n@REM reg query "HKCU\\Control Panel\\International" \/v sShortDate\r\nset LogDate=%date%\r\n\r\nset yyyy=\r\n\r\nset $tok=1-3\r\nfor \/f "tokens=1 delims=.:\/-, " %%u in ('date \/t') do set $d1=%%u\r\nif "%$d1:~0,1%" GTR "9" set $tok=2-4\r\nfor \/f "tokens=%$tok% delims=.:\/-, " %%u in ('date \/t') do (\r\nfor \/f "skip=1 tokens=2-4 delims=\/-,()." %%x in ('echo.^|date') do (\r\nset %%x=%%u\r\nset %%y=%%v\r\nset %%z=%%w\r\nset $d1=\r\nset $tok=))\r\n\r\nif "%yyyy%"=="" set yyyy=%yy%\r\nif \/I %yyyy% LSS 100 set \/A yyyy=2000 + 1%yyyy% - 100\r\n\r\nset todDate=%yyyy%-%mm%-%dd%\r\n\r\nrem Restore date old format configuration\r\nreg copy "%RegKey%-Temp" "%RegKey%" \/f>nul\r\nreg delete "%RegKey%-Temp" \/f>nul\r\n\r\nfor \/f "delims= " %%i in (\r\n'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 "," '\r\n) do set fileLoc=%%i\r\n\r\nfor \/f "delims=" %%i in (\r\n'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 "," '\r\n) do set resDate=%%i\r\n\r\nfor \/f "tokens=1-4 delims=- " %%d in ("%resDate%") do (\r\nset year=%%d\r\nset month=%%e\r\nset day=%%f\r\n)\r\nif %month% == 01 (\r\nset monthdesc=Jan\r\n) else if %month% == 02 (\r\nset monthdesc=Feb\r\n) else if %month% == 03 (\r\nset monthdesc=Mar\r\n) else if %month% == 04 (\r\nset monthdesc=Apr\r\n) else if %month% == 05 (\r\nset monthdesc=May\r\n) else if %month% == 06 (\r\nset monthdesc=Jun\r\n) else if %month% == 07 (\r\nset monthdesc=Jul\r\n) else if %month% == 08 (\r\nset monthdesc=Aug\r\n) else if %month% == 09 (\r\nset monthdesc=Sep\r\n) else if %month% == 10 (\r\nset monthdesc=Oct\r\n) else if %month% == 11 (\r\nset monthdesc=Nov\r\n) else if %month% == 12 (\r\nset monthdesc=Dec\r\n)\r\nset myDate=%day%-%monthdesc%-%year%\r\n\r\nfor \/f "tokens=1-4 delims=: " %%d in ("%resDate%") do (\r\nset hour=%%e\r\nset minute=%%f\r\nset second=%%g\r\n)\r\nset myTime=%hour%:%minute%\r\n\r\necho.\r\necho %resDate% | findstr %todDate% >nul && (\r\necho Database '%sqlDB%' is FRESH, restored today at %myTime%\r\necho Restored from '%fileLoc%'\r\n) || (\r\necho Database '%sqlDB%' is OLD, last restored on %myDate% at %myTime%\r\necho Restored from '%fileLoc%'\r\necho.\r\necho Please check if backup exist or manually restore\r\n)\r\npause>nul<\/pre>\nFeel 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":3,"featured_media":156,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[9],"tags":[15,16],"class_list":["post-133","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programming-database","tag-batch","tag-sql"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8pg5H-29","jetpack-related-posts":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/eligiblestore.com\/blog\/wp-content\/uploads\/2016\/03\/logo-sql.png?fit=810%2C441&ssl=1","_links":{"self":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts\/133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/comments?post=133"}],"version-history":[{"count":2,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts\/133\/revisions"}],"predecessor-version":[{"id":1270,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts\/133\/revisions\/1270"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/media\/156"}],"wp:attachment":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/media?parent=133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/categories?post=133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/tags?post=133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}