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.