代码改变世界

SQLServer备份信息查看

  abce  阅读(734)  评论(0编辑  收藏  举报

查看数据文件和日志文件的备份记录

1
2
3
4
5
6
7
8
9
10
11
SELECT [filegroup_name]
      ,[backed_up_page_count]
      ,[file_type]
      ,[file_size]
      ,[logical_name]
      ,[physical_name]
      ,[State]
      ,[state_desc]
      ,[backup_size]
      ,[differential_base_lsn]
 FROM [msdb].[dbo].[backupfile];

其中:
filegroup_name:日志文件没有文件组,故日志文件备份信息中,该列为null
file_type:D表示数据文件;L表示日志文件;F表示full-text catalog;S表示内存优化文件
State:0表示online;1表示restoring;2表示recovering;3表示recover pending;4表示suspect;6表示offline;7表示defunct;8表示dropped


备份后查看备份的逻辑和物理设备名称

1
select logical_device_name ,physical_device_name,device_type  FROM [msdb].[dbo].[backupmediafamily];

其中:
device_type:2表示磁盘;5表示磁带;7表示虚拟设备;9表示azure存储;105表示A permanent backup SQL database device

 

查看每个数据库的备份信息

1
2
select name,user_name,first_lsn,last_lsn,database_backup_lsn,backup_start_date,backup_finish_date,type,database_name,server_name,machine_name
FROM [msdb].[dbo].[backupset];


查看最近的全备信息

1
2
3
4
5
6
7
8
9
SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  msdb..backupset.type = 'D'
GROUP BY
   msdb.dbo.backupset.database_name;

  

查看数据库的历史备份信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
When 'I' THEN 'Differential database'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
-- WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE()-1 )
ORDER BY
msdb.dbo.backupset.backup_finish_date desc

 

查看哪些数据库没有做备份

1
2
3
4
5
6
7
8
9
SELECT     
      S.NAME AS database_name, 
      'Nobackups' AS [Backup Age (Hours)] 
FROM
   master.dbo.sysdatabases S LEFT JOIN msdb.dbo.backupset B
       ON S.name  = B.database_name
WHERE B.database_name IS NULL AND S.name <> 'tempdb'
ORDER BY 
   B.database_name;


其他

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
SELECT 
   A.[Server],  
   A.database_name, 
   A.last_db_backup_date, 
   B.backup_start_date,   
   B.backup_finish_date,
   B.expiration_date,
   B.backup_size, 
   B.logical_device_name, 
   B.physical_device_name,  
   B.backupset_name,
   B.description
FROM
   (
   SELECT  
      CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
      msdb.dbo.backupset.database_name, 
      MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM
      msdb.dbo.backupmediafamily 
      INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
   WHERE
      msdb..backupset.type = 'D'
   GROUP BY
      msdb.dbo.backupset.database_name 
   ) AS A
   LEFT JOIN 
   (
   SELECT  
      CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
      msdb.dbo.backupset.database_name, 
      msdb.dbo.backupset.backup_start_date, 
      msdb.dbo.backupset.backup_finish_date,
      msdb.dbo.backupset.expiration_date,
      msdb.dbo.backupset.backup_size, 
      msdb.dbo.backupmediafamily.logical_device_name, 
      msdb.dbo.backupmediafamily.physical_device_name,  
      msdb.dbo.backupset.name AS backupset_name,
      msdb.dbo.backupset.description
   FROM
      msdb.dbo.backupmediafamily 
      INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
   WHERE
      msdb..backupset.type = 'D'
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY 
   A.database_name

  

 

 

 

相关博文:
阅读排行:
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
历史上的今天:
2017-10-31 MySQL的reset slave与reset slave all
2017-10-31 MySQL复制中slave延迟监控
2015-10-31 Oracle 12C -- 删除PDB
2015-10-31 Oracle 12C -- plug unplugged PDB into CDB
2015-10-31 Oracle 12C -- clone a remote pdb
2015-10-31 Oracle 12C -- clone a non-cdb as a pdb
点击右上角即可分享
微信分享提示