MSSQLServer2008单表数据丢失

Question

今天遇到一个问题一直找不到原因,所有想请教社区的老大们希望得到答案：就是当MSSQLServer2008中单表A表数据达到30W条以上，而且该表并发也比较频繁，并且设置了每两小时自动备份一次。在两周内总会丢失一部分数据。丢失的部分数据也比较怪异：就是第一次丢失是在2014-10-31，丢失数据是从2014-10-31到2014-10-22，第二次是2014-11-8到2014-10-22，第三次是2014-11-14到2014-10-22，而且丢失的数据总是那几种订单类型，多人多次检查代码未有发现有错。但是在备份里都能找到丢失前的数据库，但是少了两个小时的数据。

Answer 1

What kind of backup you have? How often? What's recovery model of the db? If it's not in simple recovery model, can use third party tool to check log to find out who deleted data and how.

Answer 2

you can use these scripts to find out who is the suspect  if you are using the  sql2k8 or higher version

--批量创建审核对象

USE [master]
GO
DECLARE @DBNAME NVARCHAR(600)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DriveName NVARCHAR(100)
DECLARE @PATH NVARCHAR(800)
SET @DriveName = 'E'  --★Do


IF EXISTS ( SELECT  *
            FROM    sys.[server_file_audits]
            WHERE   NAME = 'ServerFileAudit' )
BEGIN 
ALTER SERVER AUDIT [ServerFileAudit] WITH (STATE=OFF)
DROP SERVER AUDIT [ServerFileAudit]
END 

--服务器审核对象
SET @SQL = 'CREATE SERVER AUDIT ServerFileAudit 
TO FILE(FILEPATH=''' + @DriveName + ':\DataBaseAudit\ServerAudit'',
MAXSIZE=5GB,
MAX_ROLLOVER_FILES=6) 
WITH (
ON_FAILURE=CONTINUE,
QUEUE_DELAY=1000);'
EXEC (@SQL)

ALTER SERVER AUDIT ServerFileAudit WITH(STATE =ON)


DECLARE CurDBName CURSOR
FOR
    SELECT  name
    FROM    sys.databases
    WHERE  [name] NOT IN ( 'MASTER', 'MODEL', 'TEMPDB', 'MSDB','ReportServer', 'ReportServerTempDB' ) 


OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
    
WHILE @@FETCH_STATUS = 0
    BEGIN
	    --数据库审核对象
        SET @SQL = 'CREATE SERVER AUDIT [DBFileAudit_' + @DBName + '] 
					TO FILE(FILEPATH=''' + @DriveName + ':\DataBaseAudit\DBAudit_' + @DBName+ ''',
					MAXSIZE=1GB,
					MAX_ROLLOVER_FILES=5) 
					WITH (
					ON_FAILURE=CONTINUE,
					QUEUE_DELAY=1000);'
        EXEC (@SQL)

        SET @SQL = 'ALTER SERVER AUDIT [DBFileAudit_' + @DBName
            + '] WITH(STATE =ON)'
        EXEC (@SQL)

        FETCH NEXT FROM CurDBName INTO @DBName
    END
CLOSE CurDBName
DEALLOCATE CurDBName
 

--批量创建审核对象

USE [master]
GO
DECLARE @DBNAME NVARCHAR(600)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DriveName NVARCHAR(100)
DECLARE @PATH NVARCHAR(800)
SET @DriveName = 'E'  --★Do


IF EXISTS ( SELECT  *
            FROM    sys.[server_file_audits]
            WHERE   NAME = 'ServerFileAudit' )
BEGIN 
ALTER SERVER AUDIT [ServerFileAudit] WITH (STATE=OFF)
DROP SERVER AUDIT [ServerFileAudit]
END 

--服务器审核对象
SET @SQL = 'CREATE SERVER AUDIT ServerFileAudit 
TO FILE(FILEPATH=''' + @DriveName + ':\DataBaseAudit\ServerAudit'',
MAXSIZE=5GB,
MAX_ROLLOVER_FILES=6) 
WITH (
ON_FAILURE=CONTINUE,
QUEUE_DELAY=1000);'
EXEC (@SQL)

ALTER SERVER AUDIT ServerFileAudit WITH(STATE =ON)


DECLARE CurDBName CURSOR
FOR
    SELECT  name
    FROM    sys.databases
    WHERE  [name] NOT IN ( 'MASTER', 'MODEL', 'TEMPDB', 'MSDB','ReportServer', 'ReportServerTempDB' ) 


OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
    
WHILE @@FETCH_STATUS = 0
    BEGIN
	    --数据库审核对象
        SET @SQL = 'CREATE SERVER AUDIT [DBFileAudit_' + @DBName + '] 
					TO FILE(FILEPATH=''' + @DriveName + ':\DataBaseAudit\DBAudit_' + @DBName+ ''',
					MAXSIZE=1GB,
					MAX_ROLLOVER_FILES=5) 
					WITH (
					ON_FAILURE=CONTINUE,
					QUEUE_DELAY=1000);'
        EXEC (@SQL)

        SET @SQL = 'ALTER SERVER AUDIT [DBFileAudit_' + @DBName
            + '] WITH(STATE =ON)'
        EXEC (@SQL)

        FETCH NEXT FROM CurDBName INTO @DBName
    END
CLOSE CurDBName
DEALLOCATE CurDBName
 

----查询服务器审核事件
SELECT  fgaf.[event_time] AS '触发审核的日期和时间' ,
        fgaf.sequence_number AS '单个审核记录中的记录顺序' ,
        fgaf.action_id AS '操作的 ID' ,
        fgaf.succeeded AS '触发事件的操作是否成功' ,
        fgaf.permission_bitmask AS '权限掩码' ,
        fgaf.is_column_permission AS '是否为列级别权限' ,
        fgaf.session_id AS '发生该事件的会话的 ID' ,
        fgaf.server_principal_id AS '执行操作的登录上下文 ID' ,
        fgaf.database_principal_id AS '执行操作的数据库用户上下文 ID' ,
        fgaf.target_server_principal_id AS '执行 GRANT/DENY/REVOKE 操作的服务器主体' ,
        fgaf.target_database_principal_id AS '执行 GRANT/DENY/REVOKE 操作的数据库主体' ,
        fgaf.object_id AS '发生审核的实体的 ID(服务器对象，DB,数据库对象，架构对象)' ,
        fgaf.class_type AS '可审核实体的类型' ,
        --[dactm].[class_type_desc] AS '可审核实体的类型描述' ,
        fgaf.session_server_principal_name AS '会话的服务器主体' ,
        fgaf.server_principal_name AS '当前登录名' ,
        fgaf.server_principal_sid AS '当前登录名 SID' ,
        fgaf.database_principal_name AS '当前用户' ,
        fgaf.target_server_principal_name AS '操作的目标登录名' ,
        fgaf.target_server_principal_sid AS '目标登录名的 SID' ,
        fgaf.target_database_principal_name AS '操作的目标用户' ,
        fgaf.server_instance_name AS '审核的服务器实例的名称' ,
        fgaf.database_name AS '发生此操作的数据库上下文' ,
        fgaf.schema_name AS '此操作的架构上下文' ,
        fgaf.object_name AS '审核的实体的名称' ,
        fgaf.statement AS 'TSQL 语句（如果存在）' ,
        fgaf.additional_information AS '单个事件的唯一信息，以 XML 的形式返回' ,
        fgaf.file_name AS '记录来源的审核日志文件的路径和名称' ,
        fgaf.audit_file_offset AS '包含审核记录的文件中的缓冲区偏移量'
FROM    sys.[fn_get_audit_file]('E:\DataBaseAudit\ServerAudit\S.sqlaudit',
                                DEFAULT, DEFAULT) AS fgaf
        INNER JOIN sys.[dm_audit_class_type_map] AS dactm ON [dactm].[class_type] = [fgaf].[class_type]
WHERE   fgaf.[event_time] BETWEEN '2014-11-04'
                          AND     '2014-11-06'
ORDER BY fgaf.[event_time] 


----查询某个数据库的审核事件
SELECT  fgaf.[event_time] AS '触发审核的日期和时间' ,
        fgaf.sequence_number AS '单个审核记录中的记录顺序' ,
        fgaf.action_id AS '操作的 ID' ,
        fgaf.succeeded AS '触发事件的操作是否成功' ,
        fgaf.permission_bitmask AS '权限掩码' ,
        fgaf.is_column_permission AS '是否为列级别权限' ,
        fgaf.session_id AS '发生该事件的会话的 ID' ,
        fgaf.server_principal_id AS '执行操作的登录上下文 ID' ,
        fgaf.database_principal_id AS '执行操作的数据库用户上下文 ID' ,
        fgaf.target_server_principal_id AS '执行 GRANT/DENY/REVOKE 操作的服务器主体' ,
        fgaf.target_database_principal_id AS '执行 GRANT/DENY/REVOKE 操作的数据库主体' ,
        fgaf.object_id AS '发生审核的实体的 ID(服务器对象，DB,数据库对象，架构对象)' ,
        fgaf.class_type AS '可审核实体的类型' ,
        [dactm].[class_type_desc] AS '可审核实体的类型描述' ,
        fgaf.session_server_principal_name AS '会话的服务器主体' ,
        fgaf.server_principal_name AS '当前登录名' ,
        fgaf.server_principal_sid AS '当前登录名 SID' ,
        fgaf.database_principal_name AS '当前用户' ,
        fgaf.target_server_principal_name AS '操作的目标登录名' ,
        fgaf.target_server_principal_sid AS '目标登录名的 SID' ,
        fgaf.target_database_principal_name AS '操作的目标用户' ,
        fgaf.server_instance_name AS '审核的服务器实例的名称' ,
        fgaf.database_name AS '发生此操作的数据库上下文' ,
        fgaf.schema_name AS '此操作的架构上下文' ,
        fgaf.object_name AS '审核的实体的名称' ,
        fgaf.statement AS 'TSQL 语句（如果存在）' ,
        fgaf.additional_information AS '单个事件的唯一信息，以 XML 的形式返回' ,
        fgaf.file_name AS '记录来源的审核日志文件的路径和名称' ,
        fgaf.audit_file_offset AS '包含审核记录的文件中的缓冲区偏移量'
FROM    sys.[fn_get_audit_file]('E:\DataBaseAudit\0000.sqlaudit',
                                DEFAULT, DEFAULT) AS fgaf
        INNER JOIN sys.[dm_audit_class_type_map] AS dactm ON [dactm].[class_type] = [fgaf].[class_type]
WHERE   fgaf.[event_time] BETWEEN '2014-11-04'
                          AND     '2014-11-06'
ORDER BY fgaf.[event_time] 

here is the related link：SQL Server Audit (Database Engine)

Answer 3

加审核看看

能够从备份中找到，说明不是数据没有成功写入，那么丢应该是被删除了

Answer 4

是数据库自动备份的。没有设置自动恢复，自动备份是完整备份模式,apexsql和Log Explorer试过了，都不支持SQL2k08。请问还有那些第三方工具可以查看日志呢？

Answer 5

谢谢！我尝试一下啊

Answer 6

 希望能找到删除的凶手程序，客人应该不可以删除得这么快吧！客人出不能进后台打开数据库来删除吧！他们没有帐号和密码啊！！！

Answer 7

you can't rely on the thrid party tools if your db was  in simple recovery model

buts, you can try this log software to retrieve the delete statement  in the log

here is the link: http://www.apexsql.com/

Answer 8

there is another thread  told  about the deleted data recover

http://www.cnblogs.com/lyhabc/p/3683147.html

Answer 9

这个有点复杂。。。

Answer 10

谢谢！！！ 已经增加服务器审核，需要等待一段时间才知道。。。