I was having a cleanup of some old scripts the other day when I stumbled upon a script I wrote some time ago.

I remember writing the script because from time to time I would get asked if I can restore a copy of a specific database to a particular point in time but I found more often than not these point in times tended to be quite a way back in the past prior to some oops moment, Well that’s easy right – just go query msdb and voila!  True this does give me the information I need to determine whether a particular backup was taken and where it was taken to, but it doesn’t tell me whether the files still exists on disk.

Can you really guarantee that those files will actually be where they are meant to be?

The problem is if I advise that I can get that database back to a point in time I want to be 100% certain that I have everything I need exactly where it is supposed to be, in most cases I can rely on the files being there and therefore my restore commands will be successful and I am able to achieve the point in time but….

What if the restore point in time is close to your file cleanup/archive thresholds?

Consider this scenario, I need to restore the SQLUndercoverDB database back to a point in time , for this example it’s 23rd July 2018 @ 13:00 It is essential that I get it as close to 13:00 as possible.

At this stage I can pick which ever script I like the most to either get me the information I need from msdb or better still produce a set of restore statements for me to use to achieve this point in time (my goto approach), in the past however I have assumed that all these files were physically available in their respective locations.

What if someone has decided to move a bunch of files, rename some files or even worse Deleted some files!  I know it doesn’t really happen often but you betcha it’s going to happen when you say those words ‘Yeah I can restore that database back to 13:00 – no problem’

So what do you do – go and check all the files manually to make sure they exist before giving the confirmation? did that once! never again! so that’s what kick started me in writing this script as it will get this check done in seconds, but not just that it will also give me a whole bunch of other backup related information about this database as it evolved somewhat into a pre flight check script which served a purpose at the time for rollback planning prior to deploying changes on the database.

Here is the information that the script provides:

1. Warn of restores over the top of the database since its last FULL backup
2. Show database snapshots currently against the database
3. Show the last FULL, DIFF and LOG backup for the database including the backup durations and backup age.
4. Backup file information such as backup start/finish time , file path , first LSN , Last LSN , a status column which states whether the log chain is in tact based on First and last LSN but also if the file exists on disk, and finally a file exists column which will tell you if the file still exists on disk.

So what does it look like if one of the backup files has been renamed, deleted or moved?

I will go and rename the 4th file in the list and show you:

Well so much for our 13:00 recovery point in time, looks like the backup containing information between 12:00 and 14:00 is missing so the best that can be achieved is 12:00 , can you imagine how awkward that conversation is gonna be!

The good part here is that we can see this ahead of time before finding out the hard way in the middle of a restore.

There are some caveats of the script of course:

1. It uses xp_fileexist
2. It will not handle Availability groups if you are taking backups on secondary replicas
3. It will always show file information to bring the database up to the most recent point in time.

If you are happy with the above then here is how it works:

SET @Databasename to the database name you want to check or leave as NULL for the current database context.

SET @DaysAgoToCheck to an integer value for the total days in the past you want to check for a FULL backup

SET @ExcludeDiffBackup to 0 or 1 depending on whether you want to include a DIFF backup in the results if there is one.

SET @FullCutoff  to a datetime here if you need to exclude certain full backups from your search  e.g you want a backup from the last 30 days but older than the most recent FULL , set @Fullcutoff to a datetime before that full backup start date.

The code can be found here on our GitHub Repo

Thanks for reading.