SCRIPT TO KILL ALL ACTIVE CONNECTIONS BEFORE A BACKUP OR RESTORE

Use Master

Go

Declare @dbname sysname

Set @dbname = 'IEMS'

Declare @spid int

Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname)

While @spid Is Not Null

Begin

        Execute ('Kill ' + @spid)

        Select @spid = min(spid) from master.dbo.sysprocesses

        where dbid = db_id(@dbname) and spid > @spid

End

-- RESTORE DATABASE

RESTORE DATABASE XXXX_DB FROM DISK = 'D:\Backups\XXXX_DB-20221031-190000.BAK'

GO

-- IF THE DATABASE IS TO BE RE-WRITTEN OR REPLACED

RESTORE DATABASE XXXX_DB FROM DISK = 'D:\Backups\XXXX_DB-20221031-190000.BAK'

WITH REPLACE

GO

SCRIPT TO CHECK DATABASE BACKUP OR RESTORE STATUS

USE master

GO




SELECT

req.session_id,

database_name = db_name(req.database_id),

req.status,

req.blocking_session_id,

req.command,

[sql_text] = Substring(txt.TEXT, (req.statement_start_offset / 2) + 1, (

(

CASE req.statement_end_offset

WHEN - 1 THEN Datalength(txt.TEXT)

ELSE req.statement_end_offset

END - req.statement_start_offset

) / 2

) + 1),

req.percent_complete,

req.start_time,

cpu_time_sec = req.cpu_time / 1000,

granted_query_memory_mb = CONVERT(NUMERIC(8, 2), req.granted_query_memory / 128.),

req.reads,

req.logical_reads,

req.writes,

eta_completion_time = DATEADD(ms, req.[estimated_completion_time], GETDATE()),

elapsed_min = CONVERT(NUMERIC(6, 2), req.[total_elapsed_time] / 1000.0 / 60.0),

remaning_eta_min = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0),

eta_hours = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0/ 60.0),

wait_type,

wait_time_sec = wait_time/1000,

wait_resource

FROM sys.dm_exec_requests as req WITH(NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as txt

WHERE req.session_id>50

AND command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')