精华内容
下载资源
问答
  • sql还原数据库备份数据库 This article will review how to get information on your SQL database restore history, including the metadata in MSDB that can be queried, as well as value added tools and ...

    sql还原数据库备份数据库

    This article will review how to get information on your SQL database restore history, including the metadata in MSDB that can be queried, as well as value added tools and features to the group, sort, report and export this critical information.

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

    DBA’s are responsible for maintaining the database backup and restoring activities. Suppose you have automated a process to restore the database from production instance to test instance. We might need to get the database history to get answers to the following questions.

    DBA负责维护数据库备份和还原活动。 假设您已经自动化了一个从生产实例到测试实例还原数据库的过程。 我们可能需要获取数据库历史记录才能获得以下问题的答案。

    • What is the last database restoration date?

      上次数据库还原的日期是什么?
    • What are the details of the database restoration (Source backup file, backup file creation date)?

      数据库还原的详细信息(源备份文件,备份文件创建日期)是什么?
    • Who performed the database restoration?

      谁执行了数据库还原?

    You can get the database restoration information using the system tables in the MSDB database. Below are tables in the MSDB database for the database restoration history.

    您可以使用MSDB数据库中的系统表来获取数据库还原信息。 下面是MSDB数据库中有关数据库还原历史记录的表。

    恢复历史记录 (Restore history )

    This table contains the row for each database restoration performed. In the following image, we can see the essential columns of this table:

    该表包含每个执行的数据库还原的行。 在下图中,我们可以看到此表的基本列:

    SELECT  
      [restore_date]
          ,[destination_database_name]
          ,[user_name]
          ,[backup_set_id]
          ,[restore_type]
          ,[replace]
          ,[recovery]
          ,[restart]
      FROM [msdb].[dbo].[restorehistory]
    

    We get the following database restoration history in my environment.

    在我的环境中,我们获得以下数据库还原历史记录。

    SQL database restore history

    restore_date: It shows the database restoration date.

    restore_date :显示数据库还原日期。

    destination_database_name: We can get the destination database name using this column.

    destination_database_name :我们可以使用此列获取目标数据库名称。

    user_name: it gives user name that performed the restoration for that particular database.

    user_name :它提供执行该特定数据库还原的用户名。

    backup_set_id: We can join this column with backupset table to get information about the backup file.

    backup_set_id :我们可以将此列与backupset表连接起来以获取有关备份文件的信息。

    restore_type: We can use this column to know the kind of database restoration performed on particular database.

    restore_type :我们可以使用此列来了解对特定数据库执行的数据库还原的类型。

    • D – Database

      D –数据库
    • I -Differential

      I-微分
    • L – Log

      L –日志
    • V – Verifyonly

      V –仅验证

    replace: once we execute a database restore command, we set this option to replace the existing destination database.

    replace:执行数据库还原命令后,将设置此选项以替换现有的目标数据库。

    • 1 – Specified

      1 –指定
    • 0 – Not specified

      0 –未指定

    recovery: In the database restore query, we also specify the Recovery and Norecovery option to bring the database open for users or not.

    recovery :在数据库还原查询中,我们还指定了Recovery and Norecovery选项以使数据库对用户开放还是不对用户开放。

    • 1 – RECOVERY

      1 –恢复
    • 0 – NoRecovery

      0 –无恢复

    Restart: It shows whether the restore operation specified the RESTART option or not.

    重新启动:它显示 恢复操作是否指定了RESTART选项。

    • 1-Specified

      1指定
    • 0-Not specified

      0-未指定

    恢复文件 (restorefile )

    We get the row for each restored file. We can join this table with restorehistory table on the restore_history_id column as well.

    我们为每个还原的文件获得一行。 我们也可以将此表与restore_history_id列上的restorehistory联接

    SQL database restore history - a row for each restored file

    Destination_phys_name: It gives the name of the physical file with the complete path. You will get the detail of each physical file that was restored by the backup file.

    Destination_phys_name:它提供具有完整路径的物理文件的名称。 您将获得备份文件还原的每个物理文件的详细信息。

    restorefilegroup (restorefilegroup )

    We can do filegroup restore as well in SQL Server. A FILEGROUP backup and restore allows restoring the objects related to specific filegroup only. It is useful mainly for huge databases in TB’s. Each database has Primary filegroup that contains primary data file MDF.

    我们也可以在SQL Server中进行文件组还原。 FILEGROUP备份和还原仅允许还原与特定文件组相关的对象。 它主要用于TB中的大型数据库。 每个数据库都有包含主要数据文件MDF的“主要”文件组。

    SELECT [restore_history_id]
          ,[filegroup_name]
      FROM [msdb].[dbo].[restorefilegroup]
    

    SQL database restore history - restorefilegroup

    [restore_history_id]: We can join this column with other MSDB tables to get more information.

    [restore_history_id]:我们可以将此列与其他MSDB表结合起来以获取更多信息。

    Filegroup_name: It is the name of the FILEGROUP on which restoration was performed.

    Filegroup_name 这是在其上执行还原的FILEGROUP的名称。

    Let us fetch information from the MSDB using internal tables with the following query. In this query, we join the restrehistory and restorefile tables with the backup history information tables to get complete information.

    让我们使用内部表通过以下查询从MSDB中获取信息。 在此查询中,我们将restrehistory和restorefile表与备份历史信息表结合在一起以获取完整的信息。

    SELECT
     rh.destination_database_name AS [Database],
      CASE WHEN rh.restore_type = 'D' THEN 'Database'
      WHEN rh.restore_type = 'F' THEN 'File'
       WHEN rh.restore_type = 'I' THEN 'Differential'
      WHEN rh.restore_type = 'L' THEN 'Log'
        ELSE rh.restore_type 
     END AS [Restore Type],
     rh.restore_date AS [Restore Date],
     bmf.physical_device_name AS [Source], 
     rf.destination_phys_name AS [Restore File],
      rh.user_name AS [Restored By]
    FROM msdb.dbo.restorehistory rh
     INNER JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
     INNER JOIN msdb.dbo.restorefile rf ON rh.restore_history_id = rf.restore_history_id
     INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
    ORDER BY rh.restore_history_id DESC
    GO
    

    We get the information about the database restoration as per the following screenshot.

    我们根据以下屏幕快照获取有关数据库还原的信息。

    SQL database restore history - information about database restoration

    结论 (Conclusion)

    In this article, we explored getting database restoration history. It is essential for the DBA to be familiar with the way of getting this information in a handy way.

    在本文中,我们探讨了如何获取数据库还原历史记录。 对于DBA来说,熟悉便捷获取此信息的方式至关重要。

    翻译自: https://www.sqlshack.com/how-to-get-a-sql-database-restore-history/

    sql还原数据库备份数据库

    展开全文
  • 使用sp_spaceused找出了所有表的数据大小,发现问题是SQL Server备份和还原历史表数据太大。用户经常会做日志备份,但是从来没有清除过历史记录。后来在设置了维护计划定期清除备份记录,问题就不在发生了。 ...

    曾经遇到过一个用户MSDB数据库非常大,让我帮忙查查是什么原因。使用sp_spaceused找出了所有表的数据大小,发现问题是SQL Server备份和还原历史表数据太大。用户经常会做日志备份,但是从来没有清除过历史记录。后来在设置了维护计划定期清除备份记录,问题就不在发生了。


    这里列出了查询SQL Server备份还原记录的语句以及如何清理数据库备份还原的脚本:


     

    --查询SQL Server备份历史

     

    SELECT

       CONVERT(CHAR(100),SERVERPROPERTY('Servername'))ASServer,

       msdb.dbo.backupset.database_name,

       msdb.dbo.backupset.backup_start_date,

       msdb.dbo.backupset.backup_finish_date,

       msdb.dbo.backupset.expiration_date,

       CASE msdb..backupset.type

           WHEN 'D' THEN 'Database'

           WHEN 'L' THEN 'Log'

       END ASbackup_type,

       msdb.dbo.backupset.backup_size,

       msdb.dbo.backupmediafamily.logical_device_name,

       msdb.dbo.backupmediafamily.physical_device_name,  

       msdb.dbo.backupset.nameASbackupset_name,

       msdb.dbo.backupset.description

    FROM   msdb.dbo.backupmediafamily

       INNER JOIN msdb.dbo.backupsetONmsdb.dbo.backupmediafamily.media_set_id=msdb.dbo.backupset.media_set_id

    ORDER BY

       msdb.dbo.backupset.database_name,

       msdb.dbo.backupset.backup_finish_date

     

     

    -----查询SQLServer还原历史

     

    select bus.server_nameas'server',rh.restore_date,bus.database_nameas'database',

    CAST(bus.first_lsnASVARCHAR(50))asLSN_First,

    CAST(bus.last_lsnASVARCHAR(50))asLSN_Last,

    CASE rh.[restore_type]

    WHEN 'D'THEN'Database'

    WHEN 'F'THEN'File'

    WHEN 'G'THEN'Filegroup'

    WHEN 'I'THEN'Differential'

    WHEN 'L'THEN'Log'

    WHEN 'V'THEN'Verifyonly'

    END ASrhType

    FROM msdb.dbo.backupsetbus

    INNER JOINmsdb.dbo.restorehistoryrhON rh.backup_set_id=bus.backup_set_id

     

    ---清除20120101之前所有的备份还原记录(没有参数指定只删除备份或者欢迎记录)

     

    use msdb

    go

    exec sp_delete_backuphistory@oldest_date='20121010'

     

    --删除'AdventureWorks2012'数据库的备份还原记录(没有参数指定保留日期,All或者None

     

    USE msdb;

    GO

    EXEC sp_delete_database_backuphistory@database_name='AdventureWorks2012';

     


    展开全文
  • 在企业管理器中删除数据库时提示框中有一选项:为数据库删除备份并还原历史记录,如下图,是什么意思? 我们先看两个图。 在还原数据库时,通过“数据库”或“文件组或文件”还原时,我们就可以看到我们曾经...

    在企业管理器中删除数据库时提示框中有一选项:为数据库删除备份并还原历史记录,如下图,是什么意思?

    为数据库删除备份并还原历史记录

    我们先看两个图。

    还原数据库-文件组或文件

    还原数据库-数据库

    在还原数据库时,通过“数据库”或“文件组或文件”还原时,我们就可以看到我们曾经的备份操作。这时我们直接选择要还原的备份集就可以了。

    如果我们在删除数据库时,选中了“为数据库删除备份并还原历史记录”,则该数据库对应的备份历史记录就会消失,我们就不能在“数据库”或“文件组或文件”中进行数据库还原该数据库了,因为对应的历史记录会消失,而只能在“从设备”中进行还原。

    如果我们在删除数据库时,没有选中“为数据库删除备份并还原历史记录”,那么即使数据库不存在了,这些曾经进行的备份操作历史也会保留下来。

    特别说明:选中“为数据库删除备份并还原历史记录”并不会删除备份文件,只是删除备份历史记录。一般情况下选中该选项。

    网友评论

    转载于:https://www.cnblogs.com/itecho/archive/2008/10/22/1316831.html

    展开全文
  • 查询数据库各种历史记录

    千次阅读 2019-05-05 12:35:00
    查询数据库各种历史记录 在SQL Server数据库中,从登陆开始,然后做了什么操作,以及数据库里发生了什么,大多都是有记录可循的,但是也有一些确实无从查起。 一.数据库启动记录 1.最近一次启动SQL Server...

    转自:11. 查询数据库各种历史记录

     

    在SQL Server数据库中,从登陆开始,然后做了什么操作,以及数据库里发生了什么,大多都是有记录可循的,但是也有一些确实无从查起。

    数据库启动记录

    1. 最近一次启动SQL Server的时间

    复制代码
    select sqlserver_start_time from sys.dm_os_sys_info;
    
    --也可参考系统进程创建的时间,比服务启动时间略晚(秒级)
    select login_time from sysprocesses where spid = 1 
    select login_time from sys.dm_exec_sessions where session_id = 1
    
    --也可参考tempdb数据库创建的时间,比服务启动时间略晚(秒级)
    select create_date from sys.databases 
    where database_id=2
    复制代码

     

    2. 最近几次启动SQL Server的时间

    复制代码
    --参考error log,系统默认保留6个归档,共7个文件
    exec xp_readerrorlog 0,1, N'SQL Server is starting'
    exec xp_readerrorlog 1,1, N'SQL Server is starting'
    exec xp_readerrorlog 2,1, N'SQL Server is starting'
    exec xp_readerrorlog 3,1, N'SQL Server is starting'
    exec xp_readerrorlog 4,1, N'SQL Server is starting'
    exec xp_readerrorlog 5,1, N'SQL Server is starting'
    exec xp_readerrorlog 6,1, N'SQL Server is starting'
    --之前关键字用N'Server process ID is'并不严谨,改为N'SQL Server is starting'
    复制代码

     

    3. 历史上更多次启动SQL Server的时间

    查看windows event log,SQL语句无法直接读取event log,如果想用命令行,可以试试VBS,Powershell。

    Event Viewer/Windows logs下Application 或者 System 事件里都有服务启动的记录。

     

    二. 登录数据库记录

    1. 查看error log

    默认情况下,只有失败的登录会被记录在error log里,如果想登录失败/成功都被记录到error log,需要开启如图选项:

     

    用SQL语句修改注册表,也同样可以开启,键值对应关系如下:

    0, None

    1, Failed

    2, Successful

    3, Both failed and successful

    USE [master]
    GO
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
    GO

    在error log里查看登录记录:

    exec xp_readerrorlog 0,1, N'Login', N'for user', null, null, N'DESC'

     

    2. 利用LOGON 触发器进行记录

    从SQL Server 2005 SP2开始引入了LOGON Trigger,可以用它在登录时做个记录,实现如下:

    复制代码
    --创建LOGON触发器
    CREATE database DBA
    GO
    
    USE DBA
    GO
    
    IF OBJECT_ID('login_history','U') is not null
        DROP TABLE login_history
    GO
    
    CREATE TABLE login_history
    (
    FACT_ID         bigint IDENTITY(1,1) primary key,
    LOGIN_NAME      nvarchar(1024),
    LOGIN_TIME      datetime
    )
    GO
    
    IF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger')
        DROP TRIGGER login_history_trigger ON ALL SERVER
    GO
    
    CREATE TRIGGER login_history_trigger
    ON ALL SERVER
    FOR LOGON
    AS
    BEGIN
        --IF SUSER_NAME() NOT LIKE 'NT AUTHORITY\%' AND 
        --   SUSER_NAME() NOT LIKE 'NT SERVICE\%'
        IF ORIGINAL_LOGIN() NOT LIKE 'NT AUTHORITY\%' AND
           ORIGINAL_LOGIN() NOT LIKE 'NT SERVICE\%'
        BEGIN
            INSERT INTO DBA..login_history
            VALUES(ORIGINAL_LOGIN(),GETDATE());
        END;
    END;
    GO
    
    --登录后查看记录
    SELECT * FROM login_history
    复制代码

     

    3. 实例:查询某login的最后一次登录

    系统表/试图里,并没有这样的字段记录,syslogins里accdate也是不对的,如果要查可以通过上面2个方法里的一种:

    (1) ERROR LOG,得设置记录Login Auditing 的“Both failed and successful” 选项,默认为”Failed”;

    (2) Logon Trigger;

     

    创建,修改,删除记录 (DDL)

    1. 服务器对象的创建,修改

    复制代码
    --创建数据库
    select name, create_date from sys.databases
    
    --创建,修改登录
    select name, createdate, updatedate from syslogins
    select name, create_date, modify_date from sys.server_principals 
    
    --创建,修改LOGON触发器
    select name, create_date, modify_date from sys.server_triggers 
    复制代码

     

    2. 数据库对象创建,修改

    --创建,修改数据库对象
    select name, create_date, modify_date from sys.objects 
    
    --创建,修改触发器,DDL触发器不在sys.objects里
    select name, create_date, modify_date from sys.triggers 

    注意:

    (1) 索引的创建,修改并没有记录

    复制代码
    sys.objects --里面没有0,1 之外的索引
    sys.indexes --里面没有日期
    objectproperty() --没有日期属性
    indexproperty()  --没有日期属性
    
    sys.dm_db_index_operational_stats 
    sys.dm_db_index_usage_stats
    sys.dm_db_index_physical_stats --也都没有
    
    STATS_DATE (table_id, index_id) --是索引的统计信息最后更新时间
    复制代码

    (2) 关于creator和owner

    SQL Server里只有owner,数据库里对象的owner必须是一个有效的database principal (user或者role),没有creator,很难知道是谁创建了这个对象,因为owner并不准确:

    首先,数据库对象的owner可以被修改,ALTER AUTHORIZATION或者sp_changeobjectowner都行;

    其次,就算owner没被修改过,默认情况下数据库对象的owner沿用schema的owner,除非在创建schema时特意指定了某个owner;

    最后,系统表并没有记录creator,如果想要查询,也许得利用DDL 触发器来记录。

    关于owner简单举例如下: 

     object owner

     

    3. 默认跟踪里的创建,修改,删除对象 (create, alter, drop)

    从sql server 2005开始引入了默认跟踪,这是sql server默认开启的跟踪,并定义了事件、文件大小,个数,查看定义如下:

    复制代码
    --系统定义好的默认跟踪事件
    select t.eventid, te.name
    from (select distinct eventid from sys.fn_trace_geteventinfo(1)) t
    inner join sys.trace_events te
    on t.eventid = te.trace_event_id
    
    --最多5个文件,每个文件20MB,依次滚动覆盖
    select * from sys.traces
    where id = 1
    复制代码

    示例,利用默认跟踪查看删除数据库记录如下:

    复制代码
    DECLARE @path varchar(1024)
    
    SELECT @path = path
    FROM sys.traces
    WHERE id = 1
    
    SELECT *
    FROM fn_trace_gettable(@path, default) --default读取当前所有trace文件,包括正在用的
    WHERE DatabaseName = 'DBA'
    and EventClass = 47    --46表示Create对象,47表示Drop对象,164表示修改对象
    and ObjectType = 16964 --16964表示数据库
    复制代码

    注意:

    (1) 其他对象比如表的删除等也都可以查到;

    (2) 默认跟踪返回的列值有很多定义,没有系统表记载,需要去翻帮助,比如ObjectType列值参考这个列表:

    https://msdn.microsoft.com/en-us/library/ms180953.aspx

    (3) 注意默认跟踪的时效性,5个20MB的文件,也许想要看的信息很快就被覆盖了;

    (4)  truncate table并没有被默认跟踪记录。

     

    数据库表的各种记录

    汇总一下对表的各种历史操作记录的查看:

    (1) create table, alter table记录,查看sys.objects 或者默认跟踪;

    (2) drop table记录,查看默认跟踪;

    (3) truncate table 也许只有去打开数据库log文件查看了,最后会简单介绍下;

    (4) DML操作表中数据的记录,查看sys.dm_db_index_usage_stats,如下:

    复制代码
    SELECT o.name as table_name, 
           s.last_user_seek,
           s.last_user_scan,
           s.last_user_lookup,
           s.last_user_update
    from sys.indexes i 
    left join sys.dm_db_index_usage_stats s 
    on s.object_id = i.object_id and 
       s.index_id = i.index_id 
    inner join sys.objects o
    on i.object_id = o.object_id
    where i.index_id <= 1
    and o.is_ms_shipped = 0
    order by o.name
    复制代码

    注意:动态管理视图(DMV) 中采集来的信息都是从sql server启动后开始的,也就是说重启后就没了。

     

    历史SQL语句记录

    有些数据库本身,会记录所有历史的SQL命令。比如:mysql和pgsql都有专门的log文本文件来存放所有历史的SQL命令;

    也有些数据库在保存log文本的同时,还保留最近的N条SQL命令在数据库里,以方便查询。

     

    SQL Server并没有这样的实现,只有sys.dm_exec_query_stats缓存了一部分 (sql server服务开启后执行的语句,某些不被缓存执行计划的语句并不记录)。

    这个视图主要是对执行计划的统计,包含消耗成本,运行次数等等,并没有session,user,每次被执行的时间等信息:

    复制代码
    SELECT st.text as sql_statement,
           qs.creation_time as plan_last_compiled,
           qs.last_execution_time as plan_last_executed,
           qs.execution_count as plan_executed_count,
           qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    order by total_elapsed_time/execution_count desc
    复制代码

    当然,开启跟踪,审计之类的方法,是可以记录所有操作的,但是这个开销有可能会影响系统性能,所以一般并不在生产环境启用。

     

    数据库备份还原历史记录

    备份还原的记录都在msdb里。

    1. 备份记录

    复制代码
    SELECT 
         bs.backup_set_id,
         bs.database_name,
         bs.backup_start_date,
         bs.backup_finish_date,
         CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
         CAST(DATEDIFF(second, bs.backup_start_date,
         bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
         CASE bs.[type]
             WHEN 'D' THEN 'Full Backup'
             WHEN 'I' THEN 'Differential Backup'
             WHEN 'L' THEN 'TLog Backup'
             WHEN 'F' THEN 'File or filegroup'
             WHEN 'G' THEN 'Differential file'
             WHEN 'P' THEN 'Partial'
             WHEN 'Q' THEN 'Differential Partial'
         END AS BackupType,
         bmf.physical_device_name,
         CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
         CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
         bs.server_name,
         bs.recovery_model
     FROM msdb.dbo.backupset bs
     INNER JOIN msdb.dbo.backupmediafamily bmf 
     ON bs.media_set_id = bmf.media_set_id
     ORDER BY bs.server_name,bs.database_name,bs.backup_start_date;
    GO
    复制代码

    如果server_name是本机,那么备份是在本机生成的;

    如果server_name是别的主机名,那么备份是被拿到本机做过数据库还原;

     

    2. 还原纪录

    复制代码
    SELECT 
         rs.[restore_history_id],
         rs.[restore_date],
         rs.[destination_database_name],
         bmf.physical_device_name,
         rs.[user_name],
         rs.[backup_set_id],
         CASE rs.[restore_type]
             WHEN 'D' THEN 'Database'
             WHEN 'I' THEN 'Differential'
             WHEN 'L' THEN 'Log'
             WHEN 'F' THEN 'File'
             WHEN 'G' THEN 'Filegroup'
             WHEN 'V' THEN 'Verifyonly'
         END AS RestoreType,
         rs.[replace],
         rs.[recovery],
         rs.[restart],
         rs.[stop_at],
         rs.[device_count],
         rs.[stop_at_mark_name],
         rs.[stop_before]
    FROM [msdb].[dbo].[restorehistory] rs
    INNER JOIN [msdb].[dbo].[backupset] bs
    --on rs.backup_set_id = bs.media_set_id
    ON rs.backup_set_id = bs.backup_set_id
    INNER JOIN msdb.dbo.backupmediafamily bmf 
    ON bs.media_set_id = bmf.media_set_id
    GO
    复制代码

    还原数据库的时候是会写backupset和backupmediafamily系统表的,用来记录还原所用到的备份文件信息。

     

    作业,维护计划,数据库邮件历史记录

    作业,维护计划,数据库邮件的历史记录,也都在msdb里。

    1. 作业历史记录

    复制代码
    if OBJECT_ID('tempdb..#tmp_job') is not null
        drop table #tmp_job
    
    --只取最后一次结果
    select job_id,
           run_status,
           CONVERT(varchar(20),run_date) run_date,
           CONVERT(varchar(20),run_time) run_time,
           CONVERT(varchar(20),run_duration) run_duration
      into #tmp_job
      from msdb.dbo.sysjobhistory jh1
     where jh1.step_id = 0
       and (select COUNT(1) from msdb.dbo.sysjobhistory jh2 
            where jh2.step_id = 0 
              and (jh1.job_id = jh2.job_id)
              and (jh1.instance_id <= jh2.instance_id))=1
    
    --排除syspolicy_purge_history这个系统作业
    select a.name job_name,
           case b.run_status when 0 then 'Failed'
                             when 1 then 'Succeeded'
                             when 2 then 'Retry'
                             when 3 then 'Canceled'
           else 'Unknown' 
           end as job_status,
           LEFT(run_date,4)+'-'+SUBSTRING(run_date,5,2)+'-'+RIGHT(run_date,2)
           +SPACE(1)
           +LEFT(RIGHT(1000000+run_time,6),2)+':'
                +SUBSTRING(RIGHT(1000000+run_time,6),3,2)+':'
                +RIGHT(RIGHT(1000000+run_time,6),2) as job_started_time,
           +LEFT(RIGHT(1000000+run_duration,6),2)+':'
                +SUBSTRING(RIGHT(1000000+run_duration,6),3,2)+':'
                +RIGHT(RIGHT(1000000+run_duration,6),2) as job_duration
      from msdb.dbo.sysjobs a 
      left join    #tmp_job b 
        on a.job_id=b.job_id 
     where a.name not in ('syspolicy_purge_history')
       and a.enabled = 1
     order by b.run_status asc,a.name,b.run_duration desc
    复制代码

     

    2. 维护计划历史记录

    select * from msdb..sysdbmaintplan_history
    
    --新的系统表也可以
    select * from msdb..sysmaintplan_log
    select * from msdb..sysmaintplan_logdetail

    维护计划最终是作为作业在运行的,也可以直接查看同名作业的历史记录。

     

    3. 数据库邮件历史记录

    复制代码
    --直接查系统表
    select * from msdb..sysmail_mailitems
    select * from msdb..sysmail_log
    
    --也可查看基于这2个系统表的系统视图
    select * from msdb..sysmail_allitems
    select * from msdb..sysmail_sentitems
    select * from msdb..sysmail_unsentitems
    select * from msdb..sysmail_faileditems
    select * from msdb..sysmail_event_log
    
    --更多系统表和视图
    use msdb
    GO
    select * from sys.objects 
    where name like '%sysmail%'
    and type in('U','V')
    order by type,name
    复制代码

     

    查看数据库日志文件

    数据库日志文件里对于DDL,DML操作肯定是有记录的,有2个内置函数可以用来解析,但是并不那么轻松,简单介绍如下:

    1. fn_dblog 读取当前在线的日志

    select * from fn_dblog(null,null) --2个null代表起始的日志LSN

    返回的结果集中字段定义:

    (1) AllocUnitName: 对象名

    (2) Operation: 操作类型,常见的有 'LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW'

    (3) [RowLog Contents 0], [RowLog Contents 1], 2,3,4,5: 字段内容,但是是二进制的,和dbcc page看到的类似

     

    试着查看truncate table记录如下:

    复制代码
    IF OBJECT_ID('test_truncate','U') is not null
        DROP TABLE test_truncate
    GO
    
    CREATE TABLE test_truncate(ID int)
    INSERT INTO test_truncate values(1)
    TRUNCATE TABLE test_truncate
    
    --查看truncate table记录
    select * from fn_dblog(null,null)
    where AllocUnitName like '%test_truncate%'
      and Description like 'Deallocated%'
    复制代码

     

    2. fn_dump_dblog 读取数据库备份里的日志

    参数介绍:前面两2个NULL和fn_dblog一样代表起始的日志LSN,DISK表示设备类型,1表示备份文件个数,最多64个,这里以1个文件为例:

    复制代码
    backup database DBA to disk = 'C:\backup\dba.bak'
    
    SELECT *
    FROM
        fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'C:\backup\dba.bak',
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
    GO
    复制代码

    这2个函数返回的信息量很大,如果有兴趣,不妨多做点测试。

    转载于:https://www.cnblogs.com/gered/p/10812320.html

    展开全文
  • 在SQL Server数据库中,从登陆开始,然后做了什么操作,以及数据库里发生了什么,大多都是有记录可循的,但是也有一些确实无从查起。 一. 数据库启动记录 1. 最近一次启动SQL Server的时间 select sqlserver_...
  • mysql 数据库历史记录

    2020-02-28 15:44:33
    改错数据库怎么还原 Mysql-premium 使用Navicat Premium 修改mysql数据库,数据改错后。 可点击 工具 历史日志… 查看正确的数据,然后再改回来。 如图: ...
  • (转)SQLServer查询数据库各种历史记录

    千次阅读 2018-10-20 13:43:00
    在SQL Server数据库中,从登陆开始,然后做了什么操作,以及数据库里发生了什么,大多都是有记录可循的,但是也有一些确实无从查起。 一. 数据库启动记录 1. 最近一次启动SQL Server的时间 select sqlserver_...
  • 执行backup ,restore操作会在备份和还原历史记录表中添加一些行,会导致msdb的大小增大以及查看历史记录繁琐(如系统表中存在某个已经删除的数据库的历史记录)。可以使用msdb库中的存储过程清除指定的历史记录以减小...
  • 为了帮助用户能以最快的速度还原数据库,SQLServer也在不断引入新的还原方法。SQL Server一共可以支持4个级别的数据还原:  数据库(“数据库完整还原”)级 还原和恢复整个数据库数据库还原和恢复操作期间会...
  • 使用该对话框可以查看位于指定服务器实例中的镜像数据库的镜像状态历史记录。 使用 SQL Server Management Studio 监视数据库镜像 如何启动数据库镜像监视器 选项 服务器实例 从中报告历史记录的...
  • 本主题介绍如何使用 SQL Server Management Studio 或 Transact-SQL 在 SQL Server 2012 中将 SQL Server 数据库还原到一个新位置并且可以选择重命名该数据库。您可以在同一服务器实例或不同服务器实例上将数据库移...
  • 如何查询SQL Server备份还原历史记录

    千次阅读 2010-01-30 22:45:00
    SQL Server在msdb数据中维护了一系列表,用来存储执行所有备份和还原的细节信息。即使你正在使用第三方的备份应用程序,只要这个应用程序使用SQL Server的虚拟设备接口(Virtual Device Interface---VDI)来执行备份和...
  • 一、备份和还原概述。 1、为什么要备份? 对于生产数据来讲,数据的安全性是至关重要的,任何数据的丢失都可能产生重要后果。 造成数据的丢失主要有以下几类: a,程序错误。例如:程序运行异常终止,或程序...
  • RESTORE 还原数据库

    2011-04-06 10:08:00
    通过此命令,您可以执行下列还原方案: 基于完整数据库备份还原整个数据库(完整还原)。 还原数据库的一部分(部分还原)。 将特定文件或文件组还原数据库(文件还原)。 将特定页面还原数据库(页面还原)。 ...
  • 数据库备份与还原

    2018-03-16 12:16:30
    数据备份与还原基础概念:备份,将当前已有的数据或记录另存一份;还原,将数据恢复到备份时的状态。为什么要进行数据的备份与还原?防止数据丢失;保护数据记录。数据备份与还原的方式有很多种,具体可以分为:数据...
  • sql还原数据库备份数据库 So far, we’ve discussed a lot about database backup-and-restore process. The backup database command is an online database copy of the SQL Server database and restore ...
  • sql还原数据库备份数据库 In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database. Also, discuss several T-SQL statements to derive most ...
  • 文章目录还原方案数据库级(数据库完整还原)不同恢复模式所支持的各种还原方案数据库完整还原完整数据库备份+差异备份+事务日志备份 示例参考资料 还原方案 数据库级(数据库完整还原还原和恢复整个数据库。...
  • 还原及恢复数据库

    千次阅读 2006-11-21 12:42:00
    还原及恢复数据库还原方法 执行数据库还原规划严重损坏的恢复本章总结在 第 32 章 中,我们学到了系统备份的重要性,以及如何执行备份。本章将继续上一章所提的数据库保护与建立的说明。我们将学习如何还原数据库、...
  • 还原及回复数据库还原方法 执行数据库还原规划严重损坏的回复还原及回复数据库还原方法 执行数据库还原规划严重损坏的回复本章总结在 第 32 章 中,我们学到了系统备份的重要性,以及如何执行备份。本章将继续上一章...
  • Toad挖取数据库操作日志 还原

    千次阅读 2018-11-13 15:43:56
    Toad挖取数据库操作日志 还原问题背景操作记录 问题背景 未知原因造成数据库数据某条数据丢失,现用 Toad 挖取日志。 表名: TABLE 时间: 2018-11-13 14:20:00 左右 操作记录 sql语句里有业务数据。 ...
  • 第一步右击选择要还原数据库如下图: 第二步选择数据库备份文件,如下图: 出现选择文件的对话框如下: 第三步选择“强制还原”如下图: 最后点击确定,就可还原数据库。 (注意:对一下选项菜单中的被还原的...
  • 一、还原用户数据库1、“还原数据库”基本操作(1)目标数据库 在该列表中输入要还原的数据库。您可以输入新的数据库,也可以从下拉列表中... (2)目标时间点 将数据库还原到备份的最近可用时间,或还原到特定...
  • 使用数据库备份还原数据库数据库系统崩溃时提供数据恢复最小代价的最优方案;(总不能让客户重新填报数据吧!) 没有数据就没有一切,数据库备份就是一种防范灾难于未然的强力手段; 对于DBA来说,最首要也是最...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 15,715
精华内容 6,286
关键字:

数据库的还原历史记录