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;
 

Little script to see the backup history

 Today, I was teaching a co-worker how to create a copy only backup of a SQL Managed Instance database.  And I came up to use this script to verify the schedule on when a SQL Managed Instance is being backed up or how is MS doing it.

USE msdb;
GO
 
SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.type AS backup_type_code,
    CASE bs.type
        WHEN 'D' THEN 'Full Database'
        WHEN 'I' THEN 'Differential Database'
        WHEN 'L' THEN 'Transaction Log'
        ELSE 'Other'
    END AS backup_type,
    bs.backup_size / 1024 / 1024 AS backup_size_MB,
    bmf.physical_device_name,
    bs.name AS backup_set_name,
    bs.user_name
FROM
    dbo.backupset bs
INNER JOIN
    dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE
    bs.database_name = 'YourDatabaseName'  -- Replace with your actual database name
ORDER BY
    bs.backup_finish_date DESC;
 

Cool little script if you are doing a long restore

 Here's a cool little script to see where your restore script is at.

SELECT
session_id AS SPID,
command,
percent_complete AS [Percent Complete],
start_time AS [Start Time],
DATEADD(SECOND, estimated_completion_time / 1000, GETDATE()) AS [Estimated Completion Time],
total_elapsed_time / 1000.0 / 60.0 AS [Elapsed Minutes],
estimated_completion_time / 1000.0 / 60.0 AS [Remaining Minutes]
FROM sys.dm_exec_requests
WHERE command IN ('BACKUP DATABASE', 'RESTORE DATABASE');

Hope this helps someone.

Execute as user

Hello!

Sorry, I haven't posted for a while.  Been doing so much other stuff.

Today, I was helping a user with granting permissions to her service account.  Even though I granted all her required permissions, her report still wasn't able to execute.

We kept looking on the EXECUTE permissions that she needed.  But to no avail.  

I then helped her out by running the proc using SSMS.  I needed to impersonate her service account in SSMS.  I first tried runas from the command prompt,

runas /noprofile /user:DOMAIN\SvcAccount "C:\Program Files\Microsoft SQL Server Management Studio 21\Release\Common7\IDE\SSMS.exe"

Enter the password for DOMAIN\SvcAccount

But, SSMS, for some reason just won't open.  Maybe, it is at a newer version, 21.  I really didn't try it at lower versions, too lazy LOL.

Anywho, I decided to use EXECUTE AS USER in my query,

USE [db_name]
GO
DECLARE @RC int
DECLARE @begin_dt datetime
DECLARE @end_dt datetime
-- TODO: Set parameter values here.
SET @begin_dt = '2025-06-01'
SET @end_dt = GETDATE()

EXECUTE AS USER = 'DOMAIN\SvcAccount'

EXECUTE @RC = [dbo].[sp_proc_name] 
   @begin_dt
  ,@end_dt
GO

We found out that she needed more permissions.  Without reading through hundred lines of code, we got an error that the service account needed read permissions to a separate database as it needed to join data from it.

She was very thankful for me helping her.  She said she wouldn't have known that she needed read on that database if it weren't for me.  It felt good to help someone today.

Hope this also helps someone with a similar issue.

View Resource Changes in a SQL Managed Instance

 

My coworker resolved a storage issue with one of our managed instances.  To see what he did, I went to the Azure portal, viewed the managed instance and went to “Activity Log”.

In the activity log, it will show you what has been changed in the managed instance.  Azure groups the operations, so you would have to expand the operation to see what has happened.

 

I clicked the last entry, the one that says “Succeeded”.

A dialog will pop up on the right side of the page.  This will show what the actual change was.

 

In this case, he has changed the storage value from 160 GB to 256 GB.

And we can verify this on the “Overview” or “Compute + storage” sections.