A coworker shared this awesome script to monitor backup and restore operation progress. I am not sure of the origin, but is definitely has become an essential member of my SQL scripts folder.<\/p>\n
Results returned:<\/p>\n
Command = Type of backup or restore operation,
\nText = Actual SQL Command running,
\nstart_time = When process began,
\npercent_complete = Percentage of completion,
\nrunning_time = How long the process has been running,
\nest_time_to_go = Estimated time left,
\nest_completion_time = Estimated completion time and
\ncurrent_date_time = System Date<\/p>\n
SELECT command,
\ns.text,
\nstart_time,
\npercent_complete,
\nCAST(((DATEDIFF(s,start_time,GetDate()))\/3600) as varchar) + ‘ hour(s), ‘
\n\u00a0 + CAST((DATEDIFF(s,start_time,GetDate())%3600)\/60 as varchar) + ‘min, ‘
\n\u00a0 + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ‘ sec’ as running_time,
\nCAST((estimated_completion_time\/3600000) as varchar) + ‘ hour(s), ‘
\n\u00a0 + CAST((estimated_completion_time %3600000)\/60000 as varchar) + ‘min, ‘
\n\u00a0 + CAST((estimated_completion_time %60000)\/1000 as varchar) + ‘ sec’ as est_time_to_go,
\ndateadd(second,estimated_completion_time\/1000, getdate()) as est_completion_time,
\nGETDATE() as current_date_time
\nFROM sys.dm_exec_requests r
\nCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
\nWHERE r.command in (‘RESTORE DATABASE’, ‘BACKUP DATABASE’, ‘RESTORE LOG’, ‘BACKUP LOG’)<\/p>\n","protected":false},"excerpt":{"rendered":"
A coworker shared this awesome script to monitor backup and restore operation progress. I am not sure of the origin, but is definitely has become an essential member of my SQL scripts folder.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_seo_schema_type":"","_wpcom_ai_launchpad_first_post":false,"footnotes":""},"categories":[22,5],"tags":[236,317,303,318,315,316,249,35,36,295,52,291],"class_list":["post-257","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-t-sql","tag-backup","tag-datbase","tag-dba","tag-log","tag-query","tag-restore","tag-script","tag-select","tag-sql","tag-sql-server","tag-ssms","tag-t-sql"],"jetpack_shortlink":"https:\/\/wp.me\/phfTTO-49","jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/257","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=257"}],"version-history":[{"count":10,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/257\/revisions"}],"predecessor-version":[{"id":503,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/257\/revisions\/503"}],"wp:attachment":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=257"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=257"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=257"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}