Backup and restore Progress via email

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Backup and restore Progress via email

Author

Topic

espanolanthony
Starting Member

9 Posts

Posted - 2011-04-07 : 10:07:56

I have a script that does give you the information of a Backup\Restore and tell the percentage completed or percentage remaining. Now i want to create a job and would like to run this every 5 minutes while my Restore job is running. Since the restoring database is very big (1 TB) i need to see the percentage of restore completed. and i go to the query analyser after every 20 minutes and this tells me percentage completed. Does any one know how do i set up in the job step to run every 5 minutes and send me the output of the script.

---------------
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

so from some source i got some hint and i used

exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQLAdmin',
@recipients = 'isantos@foo.net;',
@subject = 'Percentage completed',
@body = 'Some Text',
@query = 'SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')',
@execute_query_database = 'db',
@exclude_query_output = 1, @append_query_error = 1;

any idea or can someone post the exact query as now i have the Profile name as well as email too.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2011-04-07 : 10:26:20

to know the backup and restore percentage, no need to go with that big query,
select percent_complete from sys.dm_exec_requests where command like '%backup%'
select percent_complete from sys.dm_exec_requests where command like '%restore%'

you can know with these queries.

and regarding sending email, you can configure dbmail. then create a job with the above code, and work around with the DBmail. you can achieve it easily.

Arnav
Even you learn 1%, Learn it with 100% confidence.

espanolanthony
Starting Member

9 Posts

Posted - 2011-04-07 : 10:38:09

thanks but when i do what you say and run the job it emails me the details but not the output of the query

JOB RUN: 'Testing' was run on 4/7/2011 at 10:36:23 AM
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by User IBM-1\Admin. The last step to run was step 1 (sds).

WWRDBA2
Starting Member

2 Posts

Posted - 2014-04-07 : 13:50:11

I have been using the query for a few weeks, but it doesn't seem to be giving me accurate stats if I use LiteSpeed for backup/restore.

Is this because I am not running a native backup/restore?
What is happening is the query will reach 100% completion, but
my job step still takes I/O for several minutes afterward.

Thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources