Live SQL Server 2005数据库的上次修改日期

This Blog entry 包含有关如何在SQL Server 2008和2005上执行此操作的信息。

• 2008年:使用新的服务器审核功能
• 2005年:使用动态管理视图(DMV)

这有助于:

SELECT max(last_user_update) last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'YOUR_DBNAME_HERE')

您可能需要重新考虑备份策略,可以将每周完整备份作为基准,然后运行 differential backups 一周之内。当然,这一切都取决于您的服务级别协议和用于恢复的业务。

列维多斯对不起,你得到的信息不正确。因为没有对每个数据库的前一个进行排序,所以实际上没有得到最后修改的日期,所以得到的是第一条返回的记录。

请把我的成绩和你的相比较。

这是我的最新消息。

DECLARE @sqlString NVARCHAR(MAX) ,
    @union NVARCHAR(MAX) ,
    @name NVARCHAR(50),
    @Counter AS Int

SET @sqlString = ''
SET @union = ''
SET @counter = 0

DECLARE crs CURSOR
FOR
    SELECT  Name
    FROM    sys.databases
    WHERE   state = 0 
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0 
    BEGIN

        SET @counter = @counter + 1

        SET @sqlString = @sqlString + @union
        SET @sqlString = @sqlString + ' 
    SELECT  *
    FROM    ( 
        SELECT TOP 1
           ''' + @name + ''' as DBName, modify_date
        FROM [' + @name + '].sys.tables 
        ORDER BY modify_date DESC
             ) as Table' + CAST(@Counter AS VARCHAR(20))

        SET @union = '  UNION '

        FETCH NEXT FROM crs INTO @name

    END 

--PRINT @sqlString 

SET @sqlString = @sqlString + ' ORDER BY DBName ASC'
CLOSE crs ;
DEALLOCATE crs ;
EXEC(@sqlString)

上面有许多错误的代码段。我已经修复了上面的SQLEagle代码片段,现在您确实应该看到最后修改的日期——我认为修改的日期应该来自sys.objects,而不是sys.tables。我看到安德鲁·阿诺德对黛比的代码片段“完全相同”发表了评论。安德鲁显然没有运行这两个代码段,但是,或者他会知道黛比的结果比水冷器在实际提供几乎正确的结果方面的贡献得到更好的结果,但是可以根据下面的内容进一步改进。

DECLARE @sqlString NVARCHAR(MAX) ,
    @union NVARCHAR(MAX) ,
    @name NVARCHAR(50),
    @Counter AS Int

SET @sqlString = ''
SET @union = ''
SET @counter = 0

DECLARE crs CURSOR
FOR
    SELECT  Name
    FROM    sys.databases
    WHERE   state = 0 
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0 
    BEGIN

        SET @counter = @counter + 1

        SET @sqlString = @sqlString + @union
        SET @sqlString = @sqlString + ' 
    SELECT  *
    FROM    ( 
        SELECT TOP 1
           ''' + @name + ''' as DBName, modify_date
        FROM [' + @name + '].sys.objects 
        ORDER BY modify_date DESC
             ) as Table' + CAST(@Counter AS VARCHAR(20))

        SET @union = '  UNION '

        FETCH NEXT FROM crs INTO @name

    END 

--PRINT @sqlString 

SET @sqlString = @sqlString + ' ORDER BY DBName ASC'
CLOSE crs ;
DEALLOCATE crs ;
EXEC(@sqlString)

此数据库将列出服务器上的所有联机数据库和上次修改的日期。

DECLARE @sqlString NVARCHAR(max)
DECLARE @union NVARCHAR(max)
SET @sqlString = ''
SET @union = ''
DECLARE @name nvarchar(50);

DECLARE crs CURSOR FOR 
SELECT Name FROM sys.databases WHERE  state = 0
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlString = @sqlString + @union 
   SET @sqlString = @sqlString + '
    SELECT 
    TOP 1
  ''' + @name + ''' as DBName, modify_date
  FROM
   [' + @name + '].sys.tables'

 SET @union = ' UNION '

    FETCH NEXT FROM crs INTO @name
END 

SET @sqlString = @sqlString + ' ORDER BY DBName ASC'
CLOSE crs;
DEALLOCATE crs;
EXEC(@sqlString)

DECLARE @sqlString NVARCHAR(max) = ''
DECLARE @union NVARCHAR(max) = ''
DECLARE @name nvarchar(50) 

DECLARE crs CURSOR FOR 
SELECT Name FROM sys.databases WHERE  state = 0
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlString = @sqlString + @union 
   SET @sqlString = @sqlString + '
    SELECT  
  ''' + @name + ''' as DBName, Max( modify_date) as modDate
  FROM
   [' + @name + '].sys.tables'

 SET @union = ' UNION '

    FETCH NEXT FROM crs INTO @name
END 

SET @sqlString = @sqlString + ' ORDER BY modDate desc'
CLOSE crs;
DEALLOCATE crs;
EXEC(@sqlString)