CODEWITHSUNDEEP
sqlsql-serverdatabase-administration
Waller
Waller

Reputation: 1873

MSSQL backups - Can I write a script to check if the DB has been modified before backing up?

Question is in the title really, I want to know if I can check if the DB has been modified at all since the last backup, as my company change the data very sporadically and we end up with lots of backups all exactly the same.

Upvotes: 2

Views: 1084

Answers (2)

KeithS
KeithS

Reputation: 71591

The easiest way is to keep track of this fact within your schema using a "Last Updated" field. You can have a "global" value (which would be useful in your case where nothing may have changed, but if anything has you should back it up), and/or a column in each table. You can manage this field with triggers, so none of your apps have to care that the field exists, but you could get false positives with this if your apps ever perform updates to records without actually changing the data.

Then it's simple; set up the backup as a scheduled job, predicated on a query checking any and all "Last Updated" fields to see if they're newer than the last backup date (which you can also put somewhere in your schema for quick reference).

Upvotes: 0

Andomar
Andomar

Reputation: 238296

You could query the last change to any index. This includes changes to tables, since tables are stored as a special form of index (clustered or heap):

select  max(last_user_update)
from    sys.dm_db_index_usage_stats ius
join    sys.databases db
on      ius.database_id = db.database_id
where   db.name = 'YourDbName'

These statistics are reset after a server restart. You can find the server restart time with:

select  create_date
from    sys.databases
where   database_id = 2 -- tempdb

Upvotes: 2

Related Questions