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.

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

How to verify the Backup file in SQL Server.

RESTORE VERIFYONLY FROM DISK = 'B:\Backup\ALEXPPP_12102017.bak'

GO

--if you got below Result Set that means backup file is restorable.

--Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

--The path specified by "D:\SQLData\ALEXP_System.mdf" is not in a valid directory.

--Directory lookup for the file "D:\SQLData\ALEXP_Data.ndf" failed with the operating system error 2(The system cannot find the file specified.).

--Directory lookup for the file "D:\SQLData\ALEXP_Data_1.ndf" failed with the operating system error 2(The system cannot find the file specified.).

--Directory lookup for the file "D:\SQLData\ALEXP_Data_2.ndf" failed with the operating system error 2(The system cannot find the file specified.).

--Directory lookup for the file "D:\SQLData\ALEXP_Data_3.ndf" failed with the operating system error 2(The system cannot find the file specified.).

--Directory lookup for the file "L:\SQLLogs\ALEXP_Log.ldf" failed with the operating system error 2(The system cannot find the file specified.).

--The backup set on file 1 is valid.