// ---
// jupyter:
//   jupytext:
//     text_representation:
//       extension: .cs
//       format_name: light
//       format_version: '1.5'
//       jupytext_version: 1.14.4
//   kernelspec:
//     display_name: .NET (C#)
//     language: C#
//     name: .net-csharp
// ---

// + [markdown] dotnet_interactive={"language": "pwsh"}
// # Backup Validation and DBCC CHECKDB using Rubrik Live Mount
// -

// ## Backup Validatation of a database using Live Mount

// Live mount allows for near instant recovery of a database. If a database restore/export normally takes hours, then live mounting a database will take a few minutes. Live Mount does a full recovery of a database to either the same SQL Server Instance with a different database name or another SQL Server Instance with the same or different database name. The recovery of the database is much faster, because Rubrik does not need to copy the contents of the backup from the Rubrik Cluster back to the SQL Server. All of the recovery work is done on the Rubrik cluster itself. Then the database files are presented to the SQL Server Instance via a secure SMB3 share that is only accessible by the machine the share is mounted to.
//
// Live Mounting a database is great for a lot of different use cases:
// - DBA Backup validation testing
// - Object level recovery
// - Developer testing
// - DevOps Automation
// - Reporting databases
// - Database migration application smoke test validation.
//
// A key parameter is RecoveryDateTime. All dates in Rubrik are stored in UTC format. This parameter is expecting a fully qualified date and time in UTC format. example value is 2018-08-01T02:00:00.000Z. In the example below, we are pulling the latest recovery point that Rubrik knows about.
//
// **This article serves as a way to demonstrate how to use Live Mount for Backup Validation.**
//
// ***The code examples below make use of the Rubrik, SQL Server and dbatools Powershell Modules. This is meant to be an example and not the explicit way to achieve backup validation and database integrity checks. Please review this content and use as a way to write your own validation process.***

// ### Set up environment for all next steps. 

// + dotnet_interactive={"language": "pwsh"}
$Server = $Rubrik.Server.cdm02
$Token = $Rubrik.token.cdm02
$SourceSQLServerInstance = "rp-sql19s-001.perf.rubrik.com"
$SourceDatabaseName = "AdventureWorks2019"
$TargetSQLServerInstance = "rp-sql19s-001.perf.rubrik.com"
$MountedDatabaseName = "AdventureWorks2019_LiveMount"
// -

// ### Connect to the Rubrik Cluster

// + dotnet_interactive={"language": "pwsh"}
Connect-Rubrik -Server $Server -Token $Token
// -

// ### Get details about the database from the Rubrik Cluster

// + dotnet_interactive={"language": "pwsh"}
$RubrikDatabase = Get-RubrikDatabase -Name $SourceDatabaseName -ServerInstance $SourceSQLServerInstance
# $RubrikDatabase | Format-List *
// -

// ### Mount the database to a SQL Server

// The below example will live mount a database to the latest recovery point that Rubrik knows about. Depending on the recovery model of the database and the backups that have been run against the database, this could include the snapshot and the transaction log backups. 

// + dotnet_interactive={"language": "pwsh"}
$TargetInstance = Get-RubrikSQLInstance -ServerInstance $TargetSQLServerInstance
$RubrikRequest = New-RubrikDatabaseMount -id $RubrikDatabase.id `
	-TargetInstanceId $TargetInstance.id `
	-MountedDatabaseName $MountedDatabaseName `
	-recoveryDateTime (Get-date (Get-RubrikDatabase -id $RubrikDatabase.id).latestRecoveryPoint) `
    -Confirm:$false
Get-RubrikRequest -id $RubrikRequest.id -Type mssql -WaitForCompletion    
// -

// ### Confirm that database is live mounted
// A Live mount of a database is the equivalent to doing a T-SQL Restore with your native backups. SQL Server has recovered the snapshot via the SQL Server VSS Writer, and if applicable, rolled the database forward to a point in time chosen by the user. This means we have applied all transactions from the time the snapshot has happened until the point in time chosen. Once a database has been Live Mounted to a SQL Server, the database is ready for any read/write query you would like to run. 

// + dotnet_interactive={"language": "pwsh"}
$Query = "SELECT name, state_desc FROM sys.databases"
Invoke-Sqlcmd -ServerInstance $TargetSQLServerInstance -Query $Query | Format-Table
// -

// ## DBCC CHECKDB on Live Mounted Database

// #### Look Where Live Mount Database Files Reside
// A Live Mounted database is a database that resides on the Rubrik Storage. It is then presented back to the SQL Server via an secure SMB3 share. When you look at the database files, you will see they reside on a UNC path. 

// + dotnet_interactive={"language": "pwsh"}
$Query = "SELECT DB_NAME() as DB_Name
, type_desc
, name as logical_name
, physical_name
FROM sys.database_files"
Invoke-Sqlcmd -ServerInstance $TargetSQLServerInstance -Query $Query -Database $MountedDatabaseName
// -

// Because this database is sitting on a UNC path, network latency can slow down access to the files. Additionally, the files are not sitting on your production storage array, so performance will not be the same. When you do a DBCC CHECKDB, an hidden database snapshot is created on the same location as the database files. DBCC CHECKDB, then runs its checks against the hidden snapshot. In this case, they will be created on the UNC path where the live mount is residing on. 
//
// To make things peform a bit better, you should create your database snapshot of the live mounted database on the storage that is attached to the SQL Server. This will consume next to no storage on your SQL Server, but can help increase the performance of the DBCC CHECKDB operation. 

// ### Create the database snapshot based off of the live mount

// + dotnet_interactive={"language": "pwsh"}
$SnapshotName = "$($MountedDatabaseName)_DBCC"
$DefaultSQLPaths = Get-DbaDefaultPath -SqlInstance $TargetSQLServerInstance
New-DbaDbSnapshot -SQLInstance $TargetSQLServerInstance -Database $MountedDatabaseName -Path $DefaultSQLPaths.Data -Name $SnapshotName | Format-List
// -

// ### Run DBCC CHECKDB

// + dotnet_interactive={"language": "pwsh"}
 $results = Invoke-Sqlcmd -Query "dbcc checkdb(); select @@spid as SessionID;" -ServerInstance $TargetSQLServerInstance -Database $SnapshotName 
 $spid = "spid" + $results.sessionID
 Get-SqlErrorLog -ServerInstance $($TargetSQLServerInstance)  | where-object { $_.Source -eq $spid } | Sort-Object -Property Date -Descending | Select -First 1
// -

// ### Remove database snapshot

// + dotnet_interactive={"language": "pwsh"}
Remove-DbaDbSnapshot -SqlInstance $TargetSQLServerInstance -Snapshot $SnapshotName -Confirm:$false
// -

// ## Unmount the Database Live Mount

// + dotnet_interactive={"language": "pwsh"}
$RubrikDatabaseMount = Get-RubrikDatabaseMount -MountedDatabaseName $MountedDatabaseName -TargetInstanceId $TargetInstance.id
$RubrikRequest = Remove-RubrikDatabaseMount -id $RubrikDatabaseMount.id -Confirm:$false

StarcoderdataJupyterScriptsDedupFiltered