Last modification date for a live SQL Server 2005 database

Question

Is there a way to know when the last write operation has occurred in a live SQL Server 2005 database?

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.

Answer 1

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')

Answer 2

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)