functions/image/New-PSDCImage.ps1
|
function New-PSDCImage { <# .SYNOPSIS New-PSDCImage creates a new image .DESCRIPTION New-PSDCImage will create a new image based on a SQL Server database The command will either create a full backup or use the last full backup to create the image. Every image is created with the name of the database and a time stamp yyyyMMddHHmmss i.e "DB1_20180622171819.vhdx" .PARAMETER SourceSqlInstance Source SQL Server name or SMO object representing the SQL Server to connect to. This will be where the database is currently located .PARAMETER SourceSqlCredential Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use: $scred = Get-Credential, then pass $scred object to the -SourceSqlCredential parameter. Windows Authentication will be used if SqlCredential is not specified. SQL Server does not accept Windows credentials being passed as credentials. To connect as a different Windows user, run PowerShell as that user. .PARAMETER DestinationSqlInstance SQL Server name or SMO object representing the SQL Server to connect to. This is the server to use to temporarily restore the database to create the image. .PARAMETER DestinationSqlCredential Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use: $scred = Get-Credential, then pass $scred object to the -DestinationSqlCredential parameter. Windows Authentication will be used if SqlCredential is not specified. SQL Server does not accept Windows credentials being passed as credentials. To connect as a different Windows user, run PowerShell as that user. .PARAMETER DestinationCredential Allows you to login to other parts of a system like folders. To use: $scred = Get-Credential, then pass $scred object to the -DestinationCredential parameter. .PARAMETER PSDCSqlCredential Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. This works similar as SqlCredential but is only meant for authentication to the PSDatabaseClone database server and database. By default the script will try to retrieve the configuration value "psdatabaseclone.informationstore.credential" .PARAMETER ImageNetworkPath Network path where to save the image. This has to be a UNC path .PARAMETER ImageLocalPath Local path where to save the image .PARAMETER Database Databases to create an image of .PARAMETER CreateFullBackup Create a new full backup of the database. The backup will be saved in the default backup directory .PARAMETER UseLastFullBackup Use the last full backup created for the database .PARAMETER Force Forcefully execute commands when needed .PARAMETER EnableException By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch. .PARAMETER WhatIf If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run. .PARAMETER Confirm If this switch is enabled, you will be prompted for confirmation before executing any operations that change state. .NOTES Author: Sander Stad (@sqlstad, sqlstad.nl) Website: https://psdatabaseclone.org Copyright: (C) Sander Stad, sander@sqlstad.nl License: MIT https://opensource.org/licenses/MIT .LINK https://psdatabaseclone.org/ .EXAMPLE New-PSDCImage -SourceSqlInstance SQLDB1 -DestinationSqlInstance SQLDB2 -ImageLocalPath C:\Temp\images\ -Database DB1 -CreateFullBackup Create an image for databas DB1 from SQL Server SQLDB1. The temporary destination will be SQLDB2. The image will be saved in C:\Temp\images. .EXAMPLE New-PSDCImage -SourceSqlInstance SQLDB1 -DestinationSqlInstance SQLDB2 -ImageLocalPath C:\Temp\images\ -Database DB1 -UseLastFullBackup Create an image from the database DB1 on SQLDB1 using the last full backup and use SQLDB2 as the temporary database server. The image is written to c:\Temp\images #> [CmdLetBinding(SupportsShouldProcess = $true)] param( [parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [object]$SourceSqlInstance, [System.Management.Automation.PSCredential] $SourceSqlCredential, [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [object]$DestinationSqlInstance, [System.Management.Automation.PSCredential] $DestinationSqlCredential, [System.Management.Automation.PSCredential] $DestinationCredential, [System.Management.Automation.PSCredential] $PSDCSqlCredential, [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [string]$ImageNetworkPath, [string]$ImageLocalPath, [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [object[]]$Database, [switch]$CreateFullBackup, [switch]$UseLastFullBackup, [switch]$Force, [switch]$EnableException ) begin { # Check if the setup has ran if (-not (Get-PSFConfigValue -FullName psdatabaseclone.setup.status)) { Stop-PSFFunction -Message "The module setup has NOT yet successfully run. Please run 'Set-PSDCConfiguration'" return } # Get the information store $informationStore = Get-PSFConfigValue -FullName psdatabaseclone.informationstore.mode if ($informationStore -eq 'SQL') { # Get the module configurations $pdcSqlInstance = Get-PSFConfigValue -FullName psdatabaseclone.database.Server $pdcDatabase = Get-PSFConfigValue -FullName psdatabaseclone.database.name if (-not $PSDCSqlCredential) { $pdcCredential = Get-PSFConfigValue -FullName psdatabaseclone.informationstore.credential -Fallback $null } else { $pdcCredential = $PSDCSqlCredential } # Test the module database setup if ($PSCmdlet.ShouldProcess("Test-PSDCConfiguration", "Testing module setup")) { try { Test-PSDCConfiguration -SqlCredential $pdcCredential -EnableException } catch { Stop-PSFFunction -Message "Something is wrong in the module configuration" -ErrorRecord $_ -Continue } } } Write-PSFMessage -Message "Started image creation" -Level Output # Try connecting to the instance Write-PSFMessage -Message "Attempting to connect to Sql Server $SourceSqlInstance.." -Level Output try { $sourceServer = Connect-DbaInstance -SqlInstance $SourceSqlInstance -SqlCredential $SourceSqlCredential } catch { Stop-PSFFunction -Message "Could not connect to Sql Server instance $SourceSqlInstance" -ErrorRecord $_ -Target $SourceSqlInstance return } # Cleanup the values in the network path if ($ImageNetworkPath.EndsWith("\")) { $ImageNetworkPath = $ImageNetworkPath.Substring(0, $ImageNetworkPath.Length - 1) } # Make up the data from the network path try { [uri]$uri = New-Object System.Uri($ImageNetworkPath) $uriHost = $uri.Host } catch { Stop-PSFFunction -Message "The image network path $ImageNetworkPath is not valid" -ErrorRecord $_ -Target $ImageNetworkPath return } # Setup the computer object $computer = [PsfComputer]$uriHost if (-not $computer.IsLocalhost) { # Get the result for the remote test $resultPSRemote = Test-PSDCRemoting -ComputerName $computer -Credential $Credential # Check the result if ($resultPSRemote.Result) { $command = [scriptblock]::Create("Import-Module PSDatabaseClone -Force") try { Invoke-PSFCommand -ComputerName $computer -ScriptBlock $command -Credential $Credential } catch { Stop-PSFFunction -Message "Couldn't import module remotely" -Target $command return } } else { Stop-PSFFunction -Message "Couldn't connect to host remotely.`nVerify that the specified computer name is valid, that the computer is accessible over the network, and that a firewall exception for the WinRM service is enabled and allows access from this computer" -Target $resultPSRemote -Continue } } # Check if Hyper-V is enabled if (-not (Test-PSDCHyperVEnabled -HostName $uriHost -Credential $DestinationCredential)) { Stop-PSFFunction -Message "Hyper-V is not enabled on the host." -ErrorRecord $_ -Target $uriHost return } # Get the local path from the network path if (-not $ImageLocalPath) { if ($PSCmdlet.ShouldProcess($ImageNetworkPath, "Converting UNC path to local path")) { try { # Check if computer is local if ($computer.IsLocalhost) { $ImageLocalPath = Convert-PSDCLocalUncPathToLocalPath -UncPath $ImageNetworkPath -EnableException } else { $command = "Convert-PSDCLocalUncPathToLocalPath -UncPath `"$ImageNetworkPath`" -EnableException" $commandGetLocalPath = [ScriptBlock]::Create($command) $ImageLocalPath = Invoke-PSFCommand -ComputerName $computer -ScriptBlock $commandGetLocalPath -Credential $DestinationCredential if (-not $ImageLocalPath) { return } } Write-PSFMessage -Message "Converted '$ImageNetworkPath' to '$ImageLocalPath'" -Level Verbose } catch { Stop-PSFFunction -Message "Something went wrong getting the local image path" -Target $ImageNetworkPath return } } } # Check the image local path if ($PSCmdlet.ShouldProcess("Verifying image local path")) { if ((Test-DbaSqlPath -Path $ImageLocalPath -SqlInstance $SourceSqlInstance -SqlCredential $DestinationCredential) -ne $true) { Stop-PSFFunction -Message "Image local path $ImageLocalPath is not valid directory or can't be reached." -Target $SourceSqlInstance return } # Clean up the paths if ($ImageLocalPath.EndsWith("\")) { $ImageLocalPath = $ImageLocalPath.Substring(0, $ImageLocalPath.Length - 1) } $imagePath = $ImageLocalPath } # Check the database parameter if ($Database) { foreach ($db in $Database) { if ($db -notin $sourceServer.Databases.Name) { Stop-PSFFunction -Message "Database $db cannot be found on instance $SourceSqlInstance" -Target $SourceSqlInstance } } $DatabaseCollection = $sourceServer.Databases | Where-Object { $_.Name -in $Database } } else { Stop-PSFFunction -Message "Please supply a database to create an image for" -Target $SourceSqlInstance -Continue } # Set time stamp $timestamp = Get-Date -format "yyyyMMddHHmmss" } process { # Test if there are any errors if (Test-PSFFunctionInterrupt) { return } # Loop through each of the databases foreach ($db in $DatabaseCollection) { Write-PSFMessage -Message "Creating image for database $db from $SourceSqlInstance" -Level Verbose if ($PSCmdlet.ShouldProcess($db, "Checking available disk space for database")) { # Check the database size to the available disk space if ($computer.IsLocalhost) { $availableMB = (Get-PSDrive -Name $ImageLocalPath.Substring(0, 1)).Free / 1MB } else { $command = [ScriptBlock]::Create("(Get-PSDrive -Name $($ImageLocalPath.Substring(0, 1)) ).Free / 1MB") $availableMB = Invoke-PSFCommand -ComputerName $computer -ScriptBlock $commandGetLocalPath -Credential $DestinationCredential } $dbSizeMB = $db.Size if ($availableMB -lt $dbSizeMB) { Stop-PSFFunction -Message "Size of database $($db.Name) does not fit within the image local path" -Target $db -Continue } } # Setup the image variables $imageName = "$($db.Name)_$timestamp" # Setup the access path $accessPath = "$ImageLocalPath\$imageName" # Setup the vhd path $vhdPath = "$($accessPath).vhdx" if ($CreateFullBackup) { if ($PSCmdlet.ShouldProcess($db, "Creating full backup for database $db")) { # Create the backup Write-PSFMessage -Message "Creating new full backup for database $db" -Level Verbose $null = Backup-DbaDatabase -SqlInstance $SourceSqlInstance -SqlCredential $SourceSqlCredential -Database $db.Name # Get the last full backup Write-PSFMessage -Message "Trying to retrieve the last full backup for $db" -Level Verbose $lastFullBackup = Get-DbaBackupHistory -SqlServer $SourceSqlInstance -SqlCredential $SourceSqlCredential -Databases $db.Name -LastFull } } elseif ($UseLastFullBackup) { Write-PSFMessage -Message "Trying to retrieve the last full backup for $db" -Level Verbose # Get the last full backup $lastFullBackup = Get-DbaBackupHistory -SqlServer $SourceSqlInstance -SqlCredential $SourceSqlCredential -Databases $db.Name -LastFull } if ($PSCmdlet.ShouldProcess("$imageName.vhdx", "Creating the vhd")) { # try to create the new VHD try { Write-PSFMessage -Message "Create the vhd $imageName.vhdx" -Level Verbose # Check if computer is local if ($computer.IsLocalhost) { $null = New-PSDCVhdDisk -Destination $imagePath -FileName "$imageName.vhdx" } else { $command = [ScriptBlock]::Create("New-PSDCVhdDisk -Destination $imagePath -FileName '$imageName.vhdx'") $null = Invoke-PSFCommand -ComputerName $computer -ScriptBlock $command -Credential $DestinationCredential } } catch { Stop-PSFFunction -Message "Couldn't create vhd $imageName" -Target "$imageName.vhd" -ErrorRecord $_ -Continue } } if ($PSCmdlet.ShouldProcess("$imageName.vhdx", "Initializing the vhd")) { # Try to initialize the vhd try { Write-PSFMessage -Message "Initializing the vhd $imageName.vhdx" -Level Verbose # Check if computer is local if ($computer.IsLocalhost) { $diskResult = Initialize-PSDCVhdDisk -Path $vhdPath -Credential $DestinationCredential } else { $command = [ScriptBlock]::Create("Initialize-PSDCVhdDisk -Path $vhdPath") $diskResult = Invoke-PSFCommand -ComputerName $computer -ScriptBlock $command -Credential $DestinationCredential } } catch { Stop-PSFFunction -Message "Couldn't initialize vhd $vhdPath" -Target $imageName -ErrorRecord $_ -Continue } } # try to create access path try { # Check if access path is already present if (-not (Test-Path -Path $accessPath)) { if ($PSCmdlet.ShouldProcess($accessPath, "Creating access path $accessPath")) { try { # Check if computer is local if ($computer.IsLocalhost) { $null = New-Item -Path $accessPath -ItemType Directory -Force } else { $command = [ScriptBlock]::Create("New-Item -Path $accessPath -ItemType Directory -Force") $null = Invoke-PSFCommand -ComputerName $computer -ScriptBlock $command -Credential $DestinationCredential } } catch { Stop-PSFFunction -Message "Couldn't create access path directory" -ErrorRecord $_ -Target $accessPath -Continue } } } # Get the properties of the disk and partition $disk = $diskResult.Disk $partition = $diskResult.Partition if ($PSCmdlet.ShouldProcess($accessPath, "Adding access path '$accessPath' to mounted disk")) { # Add the access path to the mounted disk if ($computer.IsLocalhost) { $null = Add-PartitionAccessPath -DiskNumber $disk.Number -PartitionNumber $partition[1].PartitionNumber -AccessPath $accessPath -ErrorAction SilentlyContinue } else { $command = [ScriptBlock]::Create("Add-PartitionAccessPath -DiskNumber $($disk.Number) -PartitionNumber $($partition[1].PartitionNumber) -AccessPath $accessPath -ErrorAction SilentlyContinue") $null = Invoke-PSFCommand -ComputerName $computer -ScriptBlock $command -Credential $DestinationCredential } } } catch { Stop-PSFFunction -Message "Couldn't create access path for partition" -ErrorRecord $_ -Target $diskResult.partition } # # Create folder structure for image $imageDataFolder = "$($imagePath)\$imageName\Data" $imageLogFolder = "$($imagePath)\$imageName\Log" # Check if image data folder exist if (-not (Test-Path -Path $imageDataFolder)) { if ($PSCmdlet.ShouldProcess($accessPath, "Creating data folder in vhd")) { try { Write-PSFMessage -Message "Creating data folder for image" -Level Verbose # Check if computer is local if ($computer.IsLocalhost) { $null = New-Item -Path $imageDataFolder -ItemType Directory } else { $command = [ScriptBlock]::Create("New-Item -Path $imageDataFolder -ItemType Directory") $null = Invoke-PSFCommand -ComputerName $computer -ScriptBlock $command -Credential $DestinationCredential } } catch { Stop-PSFFunction -Message "Couldn't create image data folder" -Target $imageName -ErrorRecord $_ -Continue } } } # Test if the image log folder exists if (-not (Test-Path -Path $imageLogFolder)) { if ($PSCmdlet.ShouldProcess($accessPath, "Creating log folder in vhd")) { try { Write-PSFMessage -Message "Creating transaction log folder for image" -Level Verbose # Check if computer is local if ($computer.IsLocalhost) { $null = New-Item -Path $imageLogFolder -ItemType Directory } else { $command = [ScriptBlock]::Create("New-Item -Path $imageLogFolder -ItemType Directory") $null = Invoke-PSFCommand -ComputerName $computer -ScriptBlock $command -Credential $DestinationCredential } } catch { Stop-PSFFunction -Message "Couldn't create image data folder" -Target $imageName -ErrorRecord $_ -Continue } } } # Setup the temporary database name $tempDbName = "$($db.Name)-PSDatabaseClone" if ($PSCmdlet.ShouldProcess($tempDbName, "Restoring database")) { # Restore database to image folder try { Write-PSFMessage -Message "Restoring database $db on $DestinationSqlInstance" -Level Verbose $restore = Restore-DbaDatabase -SqlInstance $DestinationSqlInstance -SqlCredential $DestinationSqlCredential ` -DatabaseName $tempDbName -Path $lastFullBackup ` -DestinationDataDirectory $imageDataFolder ` -DestinationLogDirectory $imageLogFolder } catch { Stop-PSFFunction -Message "Couldn't restore database $db as $tempDbName on $DestinationSqlInstance" -Target $restore -ErrorRecord $_ -Continue } } # Detach database if ($PSCmdlet.ShouldProcess($tempDbName, "Detaching database")) { try { Write-PSFMessage -Message "Detaching database $tempDbName on $DestinationSqlInstance" -Level Verbose $null = Dismount-DbaDatabase -SqlInstance $DestinationSqlInstance -Database $tempDbName -SqlCredential $DestinationSqlCredential } catch { Stop-PSFFunction -Message "Couldn't detach database $db as $tempDbName on $DestinationSqlInstance" -Target $db -ErrorRecord $_ -Continue } } if ($PSCmdlet.ShouldProcess($vhdPath, "Dismounting the vhd")) { # Dismount the vhd try { Write-PSFMessage -Message "Dismounting vhd" -Level Verbose # Check if computer is local if ($computer.IsLocalhost) { # Dismount the VHD $null = Dismount-VHD -Path $vhdPath # Remove the access path $null = Remove-Item -Path $accessPath -Force } else { $command = [ScriptBlock]::Create("Dismount-VHD -Path $vhdPath") $null = Invoke-PSFCommand -ComputerName $computer -ScriptBlock $command -Credential $DestinationCredential $command = [ScriptBlock]::Create("Remove-Item -Path $accessPath -Force") $null = Invoke-PSFCommand -ComputerName $computer -ScriptBlock $command -Credential $DestinationCredential } } catch { Stop-PSFFunction -Message "Couldn't dismount vhd" -Target $imageName -ErrorRecord $_ -Continue } } # Write the data to the database $imageLocation = "$($uri.LocalPath)\$imageName.vhdx" $sizeMB = $dbSizeMB $databaseName = $db.Name $databaseTS = $lastFullBackup.Start if ($informationStore -eq 'SQL') { $query = " DECLARE @ImageID INT; EXECUTE dbo.Image_New @ImageID = @ImageID OUTPUT, -- int @ImageName = '$imageName', -- varchar(100) @ImageLocation = '$imageLocation', -- varchar(255) @SizeMB = $sizeMB, -- int @DatabaseName = '$databaseName', -- varchar(100) @DatabaseTimestamp = '$databaseTS' -- datetime SELECT @ImageID as ImageID " # Add image to database if ($PSCmdlet.ShouldProcess($imageName, "Adding image to database")) { try { Write-PSFMessage -Message "Saving image information in database" -Level Verbose $result += Invoke-DbaSqlQuery -SqlInstance $pdcSqlInstance -SqlCredential $pdcCredential -Database $pdcDatabase -Query $query -EnableException $imageID = $result.ImageID } catch { Stop-PSFFunction -Message "Couldn't add image to database" -Target $imageName -ErrorRecord $_ } } } elseif ($informationStore -eq 'File') { [array]$images = $null # Get all the images $images = Get-PSDCImage # Setup the new image id if ($images.Count -ge 1) { $imageID = ($images[-1].ImageID | Sort-Object ImageID) + 1 } else { $imageID = 1 } # Add the new information to the array $images += [PSCustomObject]@{ ImageID = $imageID ImageName = $imageName ImageLocation = $imageLocation SizeMB = $sizeMB DatabaseName = $databaseName DatabaseTimestamp = $databaseTS CreatedOn = (Get-Date -format "yyyyMMddHHmmss") } # Test if the JSON folder can be reached if (-not (Test-Path -Path "PSDCJSONFolder:\")) { $command = [scriptblock]::Create("Import-Module PSDatabaseClone -Force") try { Invoke-PSFCommand -ComputerName $computer -ScriptBlock $command -Credential $Credential } catch { Stop-PSFFunction -Message "Couldn't import module remotely" -Target $command return } } # Set the image file $jsonImageFile = "PSDCJSONFolder:\images.json" # Convert the data back to JSON $images | ConvertTo-Json | Set-Content $jsonImageFile } # Add the results to the custom object [PSDCImage]$image = New-Object PSDCImage $image.ImageID = $imageID $image.ImageName = $imageName $image.ImageLocation = $imageLocation $image.SizeMB = $sizeMB $image.DatabaseName = $databaseName $image.DatabaseTimestamp = $databaseTS return $image } # for each database } # end process end { # Test if there are any errors if (Test-PSFFunctionInterrupt) { return } Write-PSFMessage -Message "Finished creating database image" -Level Verbose } } |