Last modification date for a live SQL Server 2005 database

sql-server sql-server-2005 last-modified
Vinko Vrsalovic picture Vinko Vrsalovic · Oct 19, 2010 · Viewed 21.3k times · Source

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

Dan S picture Dan S · Oct 20, 2010

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

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More