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

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)

Answer 2

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.

Answer 3

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