Last modification date for a live SQL Server 2005 database

Is there a way to know when the last write operation has occurred in a live SQL Server 2005 datab开发者_如何学编程ase?

I currently have 30 (and growing) databases on my server. Only some of these see daily activity (but which ones get daily activity varies over time.) My backup space is limited and I'd like to do a daily backup of all "modified since last backup" databases.

In fact, I'm asking the reverse of this question. Instead of asking last write date from a backup to see if I should restore it, I want to tell last write date of a live database to see if I should back it up.

Since the backups run on the server itself, I could check the last modification time of the log, but that isn't very clean, nor I'm sure is totally reliable.

Would this help:

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

This Blog entry contains information on how to do it on SQL Server 2008 and 2005.

• On 2008: using the new Server Auditing feature
• On 2005: using Dynamic Management Views (DMV)

You might want to rethink your backup strategy, perhaps by taking a weekly full backup as a baseline and then running differential backups during the week. Of course, this all depends on your SLA with the business for recovery.

levidos sorry but you get incorrect information. Because you are not sorting your top one per database so you really aren't getting the LAST MODIFIED DATE you're getting the first returned record.

Please compare my results to yours.

Here's my update.

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)

There are a number of incorrect code segments above. I have fixed SQLEagle's code snippet above and now you should indeed see the last modified dates - I believe modified date should come from sys.objects, not sys.tables. I see Andrew Arnold commented on Debbie's code snippet "being exactly the same". Andrew clearly didn't run these two code segments however or he would know Debbie's result gets a better result than WaterCooler's contribution in terms of actually providing an almost correct result, but could be further improved as per below.

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)

This one will list all online databases on the server and the last modified date

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)