generated from MaxGripe/repository-template
-
Notifications
You must be signed in to change notification settings - Fork 0
/
backups.sql
57 lines (53 loc) · 1.41 KB
/
backups.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
/*
When was the database restored?
*/
SELECT
rs.destination_database_name,
rs.restore_date,
bmf.physical_device_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.database_name,
bs.user_name
FROM msdb.dbo.restorehistory rs
INNER JOIN msdb.dbo.backupset bs ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE rs.destination_database_name = 'my_database_name'
ORDER BY rs.restore_date DESC;
/*
Monitor ongoing RESTORE progress
*/
SELECT
r.percent_complete,
r.command,
d.name AS database_name,
d.state_desc,
r.start_time
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
JOIN
sys.databases d ON t.text LIKE '%' + d.name + '%'
WHERE
r.command = 'RESTORE DATABASE'
AND d.state_desc = 'RESTORING';
/*
History of backups from the specified database.
If @DatabaseName is an empty string, it returns backup history for all databases.
*/
DECLARE @DatabaseName NVARCHAR(255);
SET @DatabaseName = '';
SELECT
bs.media_set_id,
bs.backup_finish_date,
bs.type,
bs.backup_size,
bs.compressed_backup_size,
mf.physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS mf
ON bs.media_set_id = mf.media_set_id
WHERE (@DatabaseName = '' OR bs.database_name = @DatabaseName)
ORDER BY bs.backup_finish_date DESC;
GO