How much time left-pending to complete the restore-backup or any query in SQL Server




SELECT r.session_id AS [Session_Id]

    ,r.command AS [command type]

    ,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Completed]

    ,GETDATE() AS [Current Time]

    ,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time for this query]

    ,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed min or Time taken]

    ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min]

    ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours for this query]

    ,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) 'Statement text'

            FROM sys.dm_exec_sql_text(sql_handle)

            ))

FROM sys.dm_exec_requests r

WHERE

command like 'RESTORE%'




or  command like 'BACKUP%'

Note: You can filter by where command.

Cursor- Query run on all the databases on instance and find out how much time taking on each database

USE [JaiVermaDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

--exec [SP_EventHistory]

Create PROCEDURE [dbo].[SP_EventHistory]
AS

TRUNCATE TABLE JaiVermaDB.dbo.[EventHistory]

SET NOCOUNT ON
DECLARE @db sysname;
DECLARE @S NVARCHAR(MAX);
DECLARE @i INT;
DECLARE @tDate DATETIME; 
DECLARE @tDate2 DATETIME;

SET @i = 0
SET  @tDate2 = GETDATE()

DECLARE cur CURSOR  FAST_FORWARD FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN cur
FETCH NEXT FROM cur INTO @db
WHILE @@fetch_status=0

BEGIN

SET  @tDate = GETDATE()

SET @s=N'insert into JaiVermaDB.dbo.[EventHistory_New_Temp_Table]
SELECT  '''+@db +'''as [DBNAME],
m.username AS [User Name],
event_time AS [Event Time],
s.ordernumber AS [Order Number],
s.organizationName + ''('' + s.firstName + '' '' + s.lastName + '')'' AS [Account Name],

FROM ['+@db+'].[dbo].message_log m

LEFT JOIN ['+@db+'].[dbo].submission s on charindex(s.ordernumber,m.message,1)> 0 
LEFT JOIN ['+@db+'].[dbo].client u on u.username = m.username

WHERE event_type IN ( ''PlacedOrder'',''ModifiedOrder'')
AND message not like ''Order Error found%''
and s.ordernumber is not null
and event_time >= DATEADD(Week, DATEDIFF(Week, -1, getdate()) - 1, 0)
and event_time <=   DATEADD(ss, -1, DATEADD(week, DATEDIFF(week, 0, getdate()), 0))

ORDER BY event_time'
  
  
EXEC sp_executesql @s
--print @s 

SET @i = @i + 1
PRINT ' --- Time taken each table ' + CONVERT(VARCHAR, @i) + ' name: ' + @db + ' --- in ' + CONVERT(VARCHAR,DATEDIFF(second, @tDate, GETDATE())) + ' secs'
FETCH NEXT FROM cur INTO @db

END

CLOSE cur
DEALLOCATE cur

PRINT  ' ======= Time taken for All tables in ' + CONVERT(VARCHAR,DATEDIFF(second, @tDate2, GETDATE())) + ' secs'

How to create a snapshot database in SQL Server

Example 1: 

CREATE DATABASE GNT_JaiVermaDB_dbss ON  

( NAME = metro_Data, FILENAME = 'E:\GNT_JaiVermaDB_data_1800.ss' )  ,

( NAME = ftrow_GNT_JaiVermaDB_HelpIndex, FILENAME = 'E:\ftrow_GNT_JaiVermaDBHelpIndex_data_1800.ss' )  ,

( NAME = ftrow_GNT_JaiVermaDB_MaterialIndex, FILENAME = 'E:\ftrow_GNT_JaiVermaDB_MaterialIndex_data_1800.ss' )  

AS SNAPSHOT OF GNT_JaiVermaDB;  
GO  

Example 2: 

CREATE DATABASE JaiVermaDB_Snapshot_1800 ON ( NAME = AdventureWorks, FILENAME = 'C:\Program Files\Microsoft SQL Server\JaiVermaDB_data_1800.ss' ) AS SNAPSHOT OF JaiVermaDB; GO


Example 3:

--Creating JaiVerma_snapshot as snapshot of the JaiVermaDB with multiple filegroups
CREATE DATABASE JaiVerma_snapshot1200 ON ( NAME = SPri1_dat, FILENAME = 'C:\Program Files\data\SJVdat_1200.ss'), ( NAME = SPri2_dat, FILENAME = 'C:\Program Files\data\SJV2dt_1200.ss'), ( NAME = SGrp1Fi1_dat, FILENAME = 'C:\Program Files\data\SG1JV1dt_1200.ss'), ( NAME = SGrp1Fi2_dat, FILENAME = 'C:\Program Files\data\SG1JV2dt_1200.ss'), ( NAME = SGrp2Fi1_dat, FILENAME = 'C:\Program Files\data\SG2JV1dt_1200.ss'), ( NAME = SGrp2Fi2_dat, FILENAME = 'C:\Program Files\data\SG2Fi2dt_1200.ss')
AS SNAPSHOT OF JaiVerma_DB; GO