As a database administrator, it's your responsibility to ensure the customer data won't get lost in case of a disaster, so you either provide your own backup strategy, use a third-party script, or hire a data management provider.

With the first two options, you have full control over your environment so you can know exactly if data is protected or not. However, if you have tons of servers and databases, it is possible you assume the data is protected when it isn't, or you assume the data can be recovered when it can't. You must ask yourself when was the last time you tested recovering each and every of your your databases to prove it.

When you hire a data management provider, you can request reports of protected databases, SLA compliance, error log, etc. But how do you prove all of your databases have been already added to the tool? What is the oldest and latest point in time a database can be restored to? And when a database is decommissioned, how do you prove it has been removed from the tool?

In the end, you do care if the data is protected and from what point in time it can be recovered, no matter what is your backup strategy or solution.

In case you use Rubrik as your data management provider, they provides you with a PowerShell module that helps you identify the databases that are already protected. The script to get this information is as follows:

Import-Module Rubrik
Connect-Rubrik -Server rubrik.com.us1 -Username 'domain\username' -Password (ConvertTo-SecureString 'password' -asplaintext -force) | Out-Null
$out1=Get-RubrikDatabase | select id,instanceName,state,recoveryModel -ExpandProperty rootProperties | select id,rootName,instanceName,state,recoveryModel
$result=foreach($out in $out1) {
  Get-RubrikDatabase -id $out.id | select @{N=''rootName'';E={$out.rootName}},@{N=''instanceName'';E={$out.instanceName}},@{N=''state'';E={$out.state}},@{N=''recoveryModel'';E={$out.recoveryModel}},snapshotCount,name,oldestRecoveryPoint,latestRecoveryPoint
}

The first line imports the module assuming you have already installed it. The second line connects to your Rubrik server using your username and password. The third line gets the database id in Rubrik, the instance (default or named), the state of the database, the recovery model of the database, and expands a property to get the rootName which is the server name. The last line iterates over each database to get the additional properties: snapshotCount, name of the database, and oldest and latest recovery points.

You can match this information with your database list in any form you have it, but I want something more automated that I can query directly. In my case, my boss, Carlos Robles, already has an automated way that collects this information daily, so I just need to match the results.

The following script needs to be run in the database server where I have installed the Rubrik PowerShell module, and assumes you have a table with your database list to be accessed from a linked server:

DECLARE @cmd VARCHAR(8000), @RowNum INT, @Pos1 INT, @Pos2 INT, @Pos3 INT, @Pos4 INT, @Pos5 INT, @Pos6 INT, @Pos7 INT
SET @cmd = 'powershell.exe -command "$Host.UI.RawUI.BufferSize=New-Object Management.Automation.Host.Size(512,512); Import-Module Rubrik; Connect-Rubrik -Server rubrik.com.us1 -Username ''domain\username'' -Password (ConvertTo-SecureString ''password'' -asplaintext -force) | Out-Null; $out1=Get-RubrikDatabase | select id,instanceName,state,recoveryModel -ExpandProperty rootProperties | select id,rootName,instanceName,state,recoveryModel; $result=foreach($out in $out1) {Get-RubrikDatabase -id $out.id | select @{N=''rootName'';E={$out.rootName}},@{N=''instanceName'';E={$out.instanceName}},@{N=''state'';E={$out.state}},@{N=''recoveryModel'';E={$out.recoveryModel}},snapshotCount,name,oldestRecoveryPoint,latestRecoveryPoint}; $result | Format-Table -Property * -AutoSize | Out-String -Width 512"'
CREATE TABLE #Info ([Id] INT IDENTITY(1,1), [Text] VARCHAR(MAX), [rootName] VARCHAR(128), [instanceName] VARCHAR(128), [state] VARCHAR(15), [recoveryModel] VARCHAR(15), [snapshotCount] INT, [name] VARCHAR(128), [oldestRecoveryPoint] DATETIME, [latestRecoveryPoint] DATETIME)
INSERT INTO #Info([Text]) EXEC xp_cmdshell @cmd
UPDATE #Info SET [Text]=LTRIM(RTRIM([Text]))
SET @RowNum=3
DELETE FROM #Info WHERE [Text]='' OR [Text] IS NULL
SELECT @Pos1=CHARINDEX('-',[Text],CHARINDEX(' ',[Text])) FROM #Info WHERE [Id]=@RowNum
SELECT @Pos2=CHARINDEX('-',[Text],CHARINDEX(' ',[Text],@Pos1)) FROM #Info WHERE [Id]=@RowNum
SELECT @Pos3=CHARINDEX('-',[Text],CHARINDEX(' ',[Text],@Pos2)) FROM #Info WHERE [Id]=@RowNum
SELECT @Pos4=CHARINDEX('-',[Text],CHARINDEX(' ',[Text],@Pos3)) FROM #Info WHERE [Id]=@RowNum
SELECT @Pos5=CHARINDEX('-',[Text],CHARINDEX(' ',[Text],@Pos4)) FROM #Info WHERE [Id]=@RowNum
SELECT @Pos6=CHARINDEX('-',[Text],CHARINDEX(' ',[Text],@Pos5)) FROM #Info WHERE [Id]=@RowNum
SELECT @Pos7=CHARINDEX('-',[Text],CHARINDEX(' ',[Text],@Pos6)) FROM #Info WHERE [Id]=@RowNum
DELETE FROM #Info WHERE [Id]<=3
UPDATE #Info
   SET [rootName]=LTRIM(RTRIM(SUBSTRING([Text],0,@Pos1-1))),
       [instanceName]=LTRIM(RTRIM(SUBSTRING([Text],@Pos1,@Pos2-@Pos1-1))),
       [state]=SUBSTRING([Text],@Pos2,@Pos3-@Pos2-1),
       [recoveryModel]=SUBSTRING([Text],@Pos3,@Pos4-@Pos3-1),
       [snapshotCount]=SUBSTRING([Text],@Pos4,@Pos5-@Pos4-1),
       [name]=LTRIM(RTRIM(SUBSTRING([Text],@Pos5,@Pos6-@Pos5-1))),
       [oldestRecoveryPoint]=CASE WHEN LEN([Text])>@Pos7 THEN SUBSTRING([Text],@Pos6,@Pos7-@Pos6-1) ELSE NULL END,
       [latestRecoveryPoint]=CASE WHEN LEN([Text])>@Pos7 THEN SUBSTRING([Text],@Pos7,LEN([Text])-@Pos7-1) ELSE NULL END
UPDATE #Info SET [rootName]=SUBSTRING([rootName],0,LEN([rootName])-7) --.com.us1
UPDATE #Info SET [rootName]=CASE WHEN [rootName]='NEWSERVERNAME' THEN 'OLDSERVERNAME' ELSE [rootName] END
SELECT [ni].[rootName], [ni].[instanceName], [ni].[state], [ni].[recoveryModel], [ni].[snapshotCount], [ni].[name],
       [ni].[oldestRecoveryPoint], [ni].[latestRecoveryPoint],
       [di].[instance_name], [di].[database_name], [di].[status], [di].[RecoveryModel], [di].[LastBackupDate]
  FROM #Info [ni]
FULL OUTER JOIN [LINKEDSERVER].[CATALOG].[dbo].[DatabaseInfo] [di] ON [di].[instance_name]=[ni].[rootName]+(CASE WHEN [ni].[instanceName]='MSSQLSERVER' THEN '' ELSE '\'+[ni].[instanceName] END) AND [di].[database_name]=[ni].[name]
DROP TABLE #Info

The second line uses the technique described in this article to get the information from Rubrik. Lines 3 to 24 just parse the information and put it into their corresponding columns. Line 25 removes the domain information from the rootName, because we're going to match it with a database instance name that doesn't include the domain. Line 26 fixes a mismatch between names, in the database we have the old name while in Rubrik we have the new name.

The returned information is as follows:

You can easily view the databases not in Rubrik (where rootName is NULL), the databases that no longer should be protected in Rubrik (where instance_name is NULL), and for the databases that match, the oldest recovery point, latest recovery point, and number of snapshots, and you can take the actions that correspond.