TSQL Queries related to Database Backup and Restore

-> TSQL to monitor Backup and Restore operation,


SELECT start_time, percent_complete, DATEADD (ss,DATEDIFF
(ss,start_time,getdate()) / percent_complete * 100,start_time) AS Expected_end_time, st.text as SQL, command as Current_Command
,session_id FROM sys.dm_exec_requests er CROSS APPLY
sys.dm_exec_sql_text(er.sql_handle) st
WHERE percent_complete > 0

-> TSQL to find backup history for a database.


select b.database_name, b.type, bf.physical_device_name, b.backup_start_date,
b.backup_finish_date, b.backup_size/1024/1024/1024 as Backup_size_in_GB
from msdb..backupset b
left outer join msdb..backupmediafamily bf on bf.media_set_id = b.media_set_id
where b.type in('D') and b.database_name = 'JBDB'
order by b.backup_finish_date desc

-> TSQL to find restore history for a database.


SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bs].[server_name] as [source_server_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
,[bs].[type] as BackupType
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
where [rs].[destination_database_name]='JBDB'
ORDER BY
[rs].[restore_date] DESC

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s