首页 > 数据库 >如何获取备份 SQL 数据库历史记录

如何获取备份 SQL 数据库历史记录

时间:2023-07-31 18:15:27浏览次数:75  
标签:历史记录 name dbo 备份 backupset msdb SQL 数据库

本文将回顾如何获取有关备份 SQL 数据库历史记录的信息,包括 MSDB 中可查询的元数据,以及用于对这些关键信息进行分组、排序、报告和导出的增值工具和功能

 

每个组织都必须在其环境中定义备份策略。此类策略有助于以最小的数据丢失和最短的停机时间恢复数据库。SQL Server 在系统数据库 msdb 中维护备份历史记录。我们可能会采取不同类型的备份来达到最短恢复时间。一旦发生灾难,我们必须确切地知道如何从该内表中获取数据并制定相应的恢复计划。在本文中,我们将了解如何检索数据库备份历史记录以满足这些关键业务需求。

下面是 msdb 数据库中用于数据库备份的表。

备份文件

该表包含我们为其执行备份 SQL 数据库的数据库的每个数据或日志文件的行。在下图中,我们可以看到该表的基本列:

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] 

  

Query results from backup SQL database backupfile MSDB table

filegroup_name:显示数据或日志文件的文件组名称。因此,我们没有任何日志文件的文件组;该列可以包含 NULL 值。

backed_up_page_count:显示备份的页数。

file_type:我们可以使用该字段获取文件类型。它可以具有以下值。

  • D: SQL Server data file
  • L: SQL Server log file
  • F: Full-text catalog
  • S: Memory optimised file

Logical_name:数据库文件的逻辑名称。

Physical_name:备份物理位置。

状态:给出特定文件的状态信息。我们可以为此设置以下值。

  • 0 = ONLINE
  • 1 = RESTORING
  • 2 = RECOVERING
  • 3 = RECOVERY PENDING
  • 4 = SUSPECT
  • 6 = OFFLINE
  • 7 = DEFUNCT
  • 8 = DROPPED

backup_size: 它给出备份 SQL 数据库的大小(以字节为单位)。

Differential_base_lsn: SQL Server 对 LSN 大于 Differential_base_lsn 的盘区进行差异备份。

备份媒体家族

在此表中,我们获取有关逻辑和物理备份 SQL 数据库文件名以及进行备份的设备类型的信息:

select logical_device_name ,physical_device_name,device_type from backupmediafamily

 

在上面的截图中,我们可以有以下设备类型:

  • 2 = 磁盘
  • 5 = 胶带
  • 7 = 虚拟设备
  • 9 = Azure 存储
  • 105 = 永久备份 SQL 数据库设备

备份集

在此表中,我们可以获取有关每个数据库的成功备份的信息:

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 backupset

  

Query results from backup SQL database backupset MSDB table

  • name:备份SQL数据库集描述
  • user_name:我们可以从该列获取执行本次备份的用户
  • fist_lsn、last_lsn 和database_backup_lsn:我们从该列中获取备份LSN 信息。它可以帮助我们识别特定LSN之后的备份或为数据库准备恢复计划
  • backup_start_date 和 backup_finish_date:给出备份开始和结束时间
  • Server_name:显示服务器的实例名称
  • Machine_name:我们可以获取从中进行备份的机器的名称
  • type:我们可以使用此列值获取备份类型。这里我们有以下值

    D:完整数据库备份。

    L:日志备份

    一:数据库差异备份

我们需要连接这多个表来获取相关的备份信息。例如,我们可以使用以下查询获取昨天的备份历史记录:

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

  

 我们可能想要获取所有数据库的最新完整备份的列表:

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

  

Query results from backup SQL database MSDB tables to get most recent full backups

同样重要的是确定是否存在我们没有进行任何备份的数据库。我们可以运行以下查询来获取此类数据库的列表:

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

 

在我的测试实例中,您可以看到许多数据库没有数据库备份:

备份 SQL 数据库的查询结果显示没有备份的数据库

参考

https://www.mytecbits.com/microsoft/sql-server/getting-database-backup-history

 

 

标签:历史记录,name,dbo,备份,backupset,msdb,SQL,数据库
From: https://www.cnblogs.com/lkj371/p/17594098.html

相关文章

  • 数据库三大范式,mysql索引,事务的特性和隔离级别
    1数据库三大范式是什么数据库设计理论中的三大范式是指关系数据库中的规范化原则,目的是减少数据冗余和数据更新异常。第一范式(1NF):第一范式要求关系数据库表的每个属性都是原子性的,即每个属性不能再细分为更小的数据项。它要求将数据划分为最小的单元,避免重复或多值属性。这样......
  • mysql 查看当前正在被锁定的事务和等待锁的事务信息
    SELECTr.trx_idAS'事务ID',r.trx_stateAS'事务状态',r.trx_startedAS'事务开始时间',r.trx_wait_startedAS'等待开始时间',l.lock_tableAS'表',l.lock_indexAS'索引',l.lock_modeAS......
  • MYSQL中JSON类型介绍
    1json对象的介绍在mysql未支持json数据类型时,我们通常使用varchar、blob或text的数据类型存储json字符串,对mysql来说,用户插入的数据只是序列化后的一个普通的字符串,不会对JSON文档本身的语法合法性做检查,文档的合法性需要用户自己保证。在使用时需要先将整个json对象从数据库读......
  • SQLite的SQL语法 CREATE INDEX
    SQLite的SQL语法[目录]CREATEINDEXsql-statement ::=CREATE[UNIQUE]INDEX[IFNOTEXISTS][database-name.]index-nameONtable-name(column-name[,column-name]*)column-name ::=name[COLLATEcollation-name][ASC|DESCCREATEINDEX命令由"CREATEINDEX&qu......
  • Mysql高级6-视图
    一、视图介绍视图(View):是一种虚拟存在的表,视图中的数据并不在数据库中实际存在,行和列数据来自,定义视图时查询使用的表,并且是在使用视图时动态生成的。通俗的讲,视图只保存了查询的SQL逻辑,不保存查询的结果。 二、创建视图2.1语法create[orreplace]view视图名称......
  • 【Logstash】conf文件mysql多数据源导入elasticsearch配置说明
    #inputplugin输入插件,接收事件源input{jdbc{#定义类型_1type=>"type_1"#mysql的ip、端口以及用到的数据库名jdbc_connection_string=>"jdbc:mysql://localhost:3306/数据库名"#mysql用户名jd......
  • 用SQL实现数据稽核的简单案例
    假设有一个电商平台的数据库,其中包含订单表orders和订单明细表order_items,需要对这两张表进行数据稽核,确保订单总金额(total_amount)与订单明细表中的订单金额之和一致。创建订单表orders:CREATETABLEorders(order_idINTPRIMARYKEY,customer_idINT,order_da......
  • mysql常见错误
    1.C#在centos7的环境下面连接mysql数据库,报错Authenticationmethod'caching_sha2_password'failed.Eitheruseasecureconnection,specifytheserver'sRSApublickeywithServerRSAPublicKeyFile,orsetAllowPublicKeyRetrieval=True.DbType="MySql"......
  • MySQL DML 闪回之 binlog2sql
    一直以来,由于DBA的误操作或者业务bug,导致误删数据的情况都时有发生。当出现误删数据的情况时,从线上操作日志构造误删除的数据,或者DBA使用binlog和备份的方式恢复数据,不管哪种,都非常费时费力,并且容易出错。可能有的同学会说从从库恢复,但实时主从备份只能防止硬件问题,比如主库的......
  • mysql根据mysqlbinlog恢复找回被删除的数据库
    年初和朋友一起做了个项目,到现在还没收到钱呢,今天中午时候突然听说之前的数据库被攻击了,业务数据库全部被删除。看有没有什么办法恢复,要是恢复不了,肯定也别想拿钱了吧?READMEFORRECOVERYDATAAllyourdatabaseswasbackedup.Youneedtoemailusatxednydy@fexbox......