Checking Backup File Date Before Restoring

 Today, I got an interesting request.  I had to check a restore job for a data warehouse analyst.  He said the database wasn't restored this month.  When I checked the job, it successfully restored.  So, I had to do some further investigation.  It turns out that the backup didn't complete in time before the restore.  The job that does the backup was late, so it restored an older backup of last month.  

To solve this, I had to write a script to check the date of the backup file before running a restore.  And probably loop the job every 12 hours for about 4 times, so it catches when the backup is created, even if it is 2 days late.

Hope this script helps someone.

 
DECLARE @BackupFile NVARCHAR(255) = 'D:\Backups\backup_file.bak;
DECLARE @BackupDate DATETIME;
 
-- Get backup header info
DECLARE @Header TABLE (
    BackupName NVARCHAR(128),
    BackupDescription NVARCHAR(255),
    BackupType SMALLINT,
    ExpirationDate DATETIME,
    Compressed BIT,
    Position SMALLINT,
    DeviceType TINYINT,
    UserName NVARCHAR(128),
    ServerName NVARCHAR(128),
    DatabaseName NVARCHAR(128),
    DatabaseVersion INT,
    DatabaseCreationDate DATETIME,
    BackupSize BIGINT,
    FirstLSN NUMERIC(25,0),
    LastLSN NUMERIC(25,0),
    CheckpointLSN NUMERIC(25,0),
    DatabaseBackupLSN NUMERIC(25,0),
    BackupStartDate DATETIME,
    BackupFinishDate DATETIME,
    SortOrder SMALLINT,
    CodePage SMALLINT,
    UnicodeLocaleId INT,
    UnicodeComparisonStyle INT,
    CompatibilityLevel TINYINT,
    SoftwareVendorId INT,
    SoftwareVersionMajor INT,
    SoftwareVersionMinor INT,
    SoftwareVersionBuild INT,
    MachineName NVARCHAR(128),
    Flags INT,
    BindingID UNIQUEIDENTIFIER,
    RecoveryForkID UNIQUEIDENTIFIER,
    Collation NVARCHAR(128),
    FamilyGUID UNIQUEIDENTIFIER,
    HasBulkLoggedData BIT,
    IsSnapshot BIT,
    IsReadOnly BIT,
    IsSingleUser BIT,
    HasBackupChecksums BIT,
    IsDamaged BIT,
    BeginsLogChain BIT,
    HasIncompleteMetaData BIT,
    IsForceOffline BIT,
    IsCopyOnly BIT,
    FirstRecoveryForkID UNIQUEIDENTIFIER,
    ForkPointLSN NUMERIC(25,0),
    RecoveryModel NVARCHAR(60),
    DifferentialBaseLSN NUMERIC(25,0),
    DifferentialBaseGUID UNIQUEIDENTIFIER,
    BackupTypeDescription NVARCHAR(60),
    BackupSetGUID UNIQUEIDENTIFIER,
    CompressedBackupSize BIGINT,
    Containment TINYINT
);
 
INSERT INTO @Header
EXEC('RESTORE HEADERONLY FROM DISK = ''' + @BackupFile + '''');
 
SELECT @BackupDate = BackupFinishDate FROM @Header;
 
-- Check if backup is from current month
IF MONTH(@BackupDate) = MONTH(GETDATE()) AND YEAR(@BackupDate) = YEAR(GETDATE())
BEGIN
    PRINT 'Backup is from current month. Proceeding with restore...';
 

    RESTORE DATABASE MyDatabase
    FROM DISK = @BackupFile
    WITH REPLACE, RECOVERY;

END
ELSE
BEGIN
    PRINT 'Backup is NOT from current month. Restore skipped.';
END;