Last Modified Date for SQL Server 2005 Database

Question

Is there a way to find out when the last write operation in the Live SQL Server 2005 database occurred?

I currently have 30 (and growing) databases on my server. Only some of them see daily activity (but which ones change daily, change over time). My backup space is limited, and I would like to make a daily backup of all the databases “since the last backup”.

In fact, I am asking about the opposite of this question. Instead of asking for the latest recording date from the backup to see if it should be restored, I want to report the latest recording date in the database to see if I should support it.

Since backups are run on the server itself, I can check the recent modification of the log, but this is not very clean, and I'm sure it is absolutely reliable.

Answer 1

This blog entry provides information on how to do this on SQL Server 2008 and 2005.

• In 2008: Using the New Server Audit Feature
• 2005: Using Dynamic Management Views (DMV)

Answer 2

Did it help:

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

Answer 3

You might want to rethink your backup strategy, perhaps by choosing a weekly full backup as your baseline, and then run differential backups within a week. Of course, it all depends on your SLA with a recovery business.

Answer 4

levidos sorry, but you get the wrong information. Since you are not sorting your first into one database, so you really are not getting LAST MODIFIED DATE, you are getting the first returned record.

Please compare my results with yours.

Here is 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) 

Answer 5

There are several incorrect code segments. I have fixed the SQLEagle code snippet above, and now you really should see the last changed dates - I believe that the changed date should come from sys.objects, and not from sys.tables. I see that Andrew Arnold commented on the Debbie code snippet “exactly the same”. However, Andrew clearly did not execute these two code segments, or he would have known that 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 according to 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) 

Answer 6

This list 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) 

Answer 7

 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)