SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Backup and restore Progress via email
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

espanolanthony
Starting Member

9 Posts

Posted - 04/07/2011 :  10:07:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1274 Posts

Posted - 04/07/2011 :  10:26:20  Show Profile  Send sunsanvin a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

espanolanthony
Starting Member

9 Posts

Posted - 04/07/2011 :  10:38:09  Show Profile  Reply with Quote
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).
Go to Top of Page

WWRDBA2
Starting Member

1 Posts

Posted - 04/07/2014 :  13:50:11  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000