SQL Server has a built in way to check when the database was last backed up. You can access this data in a couple of ways:
Via T-SQL - query the sys.databases table. You can identify the last time the database had a full, differential or transaction log backup
You can also use a Policy Based Management policy to do this - I use this to at least ensure that each database has a full or differential backup from within the last day, and as a prompt to act if it doesn't.
You can of course use a number of means to back up, but the only way of testing the backup properly is to try restoring it. By running a restore you check the backup, and of course the backup chain in the case of transaction log backups.
It's only by testing the restore that you can be sure that the backup is any use - and that your data is safe. So the follow on process is to ensure that you run consistency checks (DBCC CHECKDB in SQL Server) on the restored version.
Now we know:
- The database was backed up
- It can be restored to when the backup was taken
- The restored database is not corrupt
- By implication, the original database wasn't corrupt when the backup was taken
This means it is a backup that you, and your business can rely on.
The next step is to work with your backup software to automate this process, so you can be sure that the backup meets this criteria each time it runs.
