Articles by "Restore"

Showing posts with label Restore. Show all posts

December 04, 2022 1 ,
Monitoring Estimated Completion Times for Backup, Restore, and DBCC Commands in SQL Server In the world of database administration, long-running operations like backups, restores, and DBCC commands can leave us staring at the screen, wondering how much longer they’ll take. To alleviate this uncertainty, I’ve got you covered with a script that monitors the progress and provides estimated completion times for these critical tasks. 

 [code type="SQL"] SELECT @@servername[Servername],getdate() [TimeNow],command, r.session_id, r.blocking_session_id, s.text, start_time, percent_complete, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, ' + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time, CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), ' + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, ' + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go, dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s WHERE r.command in ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')[/code] 
This code can be executed SQL server 2008 or later.


Tsql code: 

SELECT @@servername[Servername],getdate() [TimeNow],command, r.session_id, r.blocking_session_id,
s.text,
start_time,
percent_complete, 
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in  ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')

Read more »

March 20, 2020 ,

 we can use below script to get last database name and date by executing below script

SELECT bus.database_name Org_DBName,
       restored_to_dbname,
       last_date_restored
FROM   msdb..backupset bus
       INNER JOIN (SELECT backup_set_id,
                          restored_to_dbname,
                          last_date_restored
                   FROM   msdb..restorehistory
                          INNER JOIN (SELECT rh.destination_database_name
                                             Restored_To_DBName,
                                             Max(rh.restore_date)
                                             Last_Date_Restored
                                      FROM   msdb..restorehistory rh
                                      GROUP  BY rh.destination_database_name) AS
                                     InnerRest
                                  ON destination_database_name =
                                     restored_to_dbname
                                     AND restore_date = last_date_restored) AS
                  RestData
               ON bus.backup_set_id = RestData.backup_set_id 


Read more »

February 26, 2020 , ,

 we can use the below script to check database backup restore percentage details and many more.



use master;

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','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')

Read more »
Subscribe to: Posts (Atom)

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.