How long it will take to complete this backup or Restore ?

Hello Everyone,

 I am going to discussing  about most useful  DMV command,which is Sys.dm_exec_requests.
Every DBA should have to get knowledge as this DMV can use frequently on daily activities.

Select * from Sys.dm_exec_requests   ---

Important columns in this DMV.

• Session_id – Session ID, which initiated the BACKUP / RESTORE Operation
• START_TIME- When the backup or restore operations  were actually started
• Status – Current Status of process
• command –what command  is going on  (BACKUP / RESTORE)
• percent_complete  - How much percentage completed per particular tasks such as Backup, Restore,shrink, Checkdb
• estimated_completion_time  - What is expected time to complete this backup operation( it could be in milliseconds( Estimated_completion\1000 =seconds , estimated_completion\1000\60= minutes) 

Query for finding ,how much percentage completed for particular tasks  such as Backup,Restore and Shrink DB.

select percent_complete  from sys.dm_exec_requests where  command='BACKUP DATABASE' or command='RESTORE DATABASE'

Query for finding the Estimated time to complete the Backup and restore 

SELECT

    session_id,

    start_time,

    status,

    command,

    percent_complete,

    estimated_completion_time,

    estimated_completion_time /60/1000 as estimate_completion_minutes,

   FROM    sys.dm_exec_requests where command = 'BACKUP DATABASE'  or command = 'RESTORE DATABASE'

Output as below..!!

Thanks&Regards

ASR

Editions of SQL Server 2008 R2

Editions of sql server 2008 r2 from Atcheswara Reddy Mule

Issue when overwritting the Existing SQL Server database(Restore Issue)

Hi All,

Most of the cases ,I have seen this restore issue..this might be occur's ,when overwriting the existing database from  production backup file to dev/stage environment..Errors details as shown below.

 

above error means, database using by another porccess so that might be  running or sleeping connections.

we need to check by usinng below query ,what are the connections are opened on particular database .

 

Select * from sys.sysprocesses where db_name(dbid) like 'dbname'

 

by using particular query we can find out ,what are the SPID's opened on particular database .output is as shown below,if connections are opened .

 

when  you are trying to restore database  ,SPID 56 connected to that particular database .that's why unable to overwrite the existing database.

 KIll that 56 SPID by using below query.

KILL 56

then try to restore the database again.it would be success.

2nd method :

first Keep database in single user mode by using below query .

Alter database dbname set Single_User with rollback immediate

then resotre the database .

changed database to multi user mode.

Alter database dbname set multi_User


Thanks
Asr