SQLServer备份信息查看
2022-10-31 10:53 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 |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 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