On my recent journey helping customers migrate from TFS to VSTS; one of the most common obstacle is verifying that users marked for active import to VSTS have matching AAD records. Thankfully there is a fairly streamlined way to do this using PowerShell and the Azure Graph API.
The magic is in the bellow PowerShell script. Feel free to copy and use it at your convenience.
#Author: Chev Bryan
#Company: Microsoft Corp.
#Date: May 01, 2019 00:00:01 EST
##First time running the script,
##ensure the AzureAD and MSOnline Modules are installed
#Connect to the MSOnline Service
#Please note: You must call the Connect-MsolService cmdlet before calling any other cmdlets.
#Fetch user by Principal Name (email address)
#Be sure to replace the email@example.com with the email address of the user you wish to fetch
Get-MsolUser -UserPrincipalName firstname.lastname@example.org
Please note, the first time you run this script, you may have to install the "AzureAD" and "MSOnline" Modules. To do that, simply un-comment the following lines from the code snippet above:
You can copy and run this script from PowerShell ISE, or save it and run it directly from the PowerShell command line.
The most likely way to do this in my experience is to take a look at the most recent backup job. This can be done quite easily from a T-SQL script. Feel free to refine the bellow to suite your needs, but it should get you the basic information you require.
SELECT TOP 100 s.server_name AS [SQL Server Name], s.database_name AS [Database Name],
m.physical_device_name AS [Backup File Physical Location],
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + ' MB' AS [Size of Backup File (MB)],
s.backup_start_date AS [Start Time], s.backup_finish_date AS [End Time],
CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) AS [Backup Duration (Seconds)],
CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS [Type of Backup],
s.recovery_model AS [Recovery Model]
FROM msdb.dbo.backupset AS s INNER JOIN msdb.dbo.backupmediafamily AS m ON s.media_set_id = m.media_set_id
WHERE (s.database_name LIKE 'tfs_%')
ORDER BY [Start Time] DESC, [End Time]
A special thank you to SQL Authority for the base script, with a couple tweaks, I was able to get the exact results I wanted.