T-SQL Query to retrieve the last full backup (Including split and copy only), Size and Location

Question

I am using the following query answered by Marcello Miorelli. This query serves the purpose for normal backups but fails to determine the actual backup size of all the split backups and copy only backups. Ideally, I want to have the list of all the database backup information (Size, Last backup date, location) including split and copy-Only backups.
Can anyone help me tweak this bug to fetch the accurate database backup size.

Tibor Karaszi · Answer

For copy only backups, just remove this condition in the WHERE clause:
AND A.is_copy_only = 0

By "split backup", I assume you mean that you have something that is using VSS to create a snapshot backup? Are you 100% certain that this isn't in the result? I don't have such a backuphistory to play with at the moment, but I suggest you start by doing SELECT from backupset and see if those backups are present. If they are, it should be pretty easy to adjust the SQL based on that. If they aren't, then you probably do a snapshot backup without SQL server knowing it, and those aren't considered valid backups from SQL Server's viewpoint (potentially corrupt databases if you restore from such a backup).

T-SQL Query to retrieve the last full backup (Including split and copy only), Size and Location

One Answer

Add your own answers!

Ask a Question