WIP/Get-DatabaseRestoreScript3.ps1
|
Function Get-DatabaseRestoreScript3 { <# .SYNOPSIS Script out TSQL RESTORE code for databases during Database Restore Activity .DESCRIPTION This function accepts backup path, data and log directory for restore operation on destination sql instance, and create RESTORE script for database restore/migration activity. It can be used for performing database restore operation with latest available backups on BackupPath. Can be used to restore database for Point In Time. Can be used for restoring database with new name on Destination SQL Instance. .PARAMETER BackupPath The directory or path where database backups are kept. It can find backup files even under child folders. Say, we provide BackupPth = 'E:\Backups', and backups for [Test] database are under 'E:\Backups\Test' folder. So the function can consider even child items as well. .PARAMETER RestoreCategory Based on selected RestoreCategory, this function can generated TSQL Restore code for either for latest application Full, Diff & TLog, or only for either Full or Diff. This function only provides functionality of Point In Time database restore. .PARAMETER StopAtTime Provide this parameter value when RestoreCategory of 'PointInTime' is selected. The parameter value accepts datetime value in format 'yyyy-MM-dd hh:mm:ss'. .PARAMETER Destination_SQLInstance Instance name of target sql server instance. For example SQL-A\sql2012 instance .PARAMETER ExecuteDirectly_DonotScriptout With this switch, the created TSQL Restore code is directly executed against Destination_SQLInstance. .PARAMETER Overwrite With this switch, the TSQL RESTORE code generated will be generated with REPLACE option. .PARAMETER ScanBackupsOnDisk With this swtich, backups from local disk are prefe .PARAMETER NoRecovery With this switch, the TSQL RESTORE code generated will be generated with NORECOVERY option. .PARAMETER DestinationPath_Data Parameter to accept Data files path or directory on target/destination SQL Server Instance. .PARAMETER DestinationPath_Log Parameter to accept Log files path or directory on target/destination SQL Server Instance. .PARAMETER SourceDatabase Accepts multiple database names separated by comma (,). Should not be used when RESTORE script is to be generated for all databases. When database names specified here, then RESTORE tsql code is generated only for those database. When used with '-Skip_Databases' switch, then the RESTORE script is NOT generated for databases mentioned. Accepts single database name when used with parameter '-RestoreAs'. .PARAMETER RestoreAs Accepts new name for single database when '-SourceDatabase' it has to be restored as new New on Destination SQL Instance. .EXAMPLE C:\PS> Script-SQLDatabaseRestore -BackupPath \\SQLBackups\ProdSrv01 -Destination_SQLInstance ProdSrv02 -DestinationPath_Data F:\mssqldata\Data -DestinationPath_Log E:\Mssqldata\Log -RestoreCategory LatestAvailable Generates RESTORE tsql code for all database with latest Full/Diff/TLog backups from path '\\SQLBackups\ProdSrv01'. .EXAMPLE C:\PS> Script-SQLDatabaseRestore -BackupPath \\SQLBackups\ProdSrv01 -Destination_SQLInstance ProdSrv02 -DestinationPath_Data F:\mssqldata\Data -DestinationPath_Log E:\Mssqldata\Log -RestoreCategory LatestAvailable -SourceDatabase Cosmo,DBA Generates RESTORE tsql code for [Cosmo] and [DBA] databases with latest Full/Diff/TLog backup from path '\\SQLBackups\ProdSrv01'. .EXAMPLE C:\PS> Script-SQLDatabaseRestore -BackupPath \\SQLBackups\ProdSrv01 -Destination_SQLInstance ProdSrv02 -DestinationPath_Data F:\mssqldata\Data -DestinationPath_Log E:\Mssqldata\Log -RestoreCategory LatestAvailable -Skip_Databases -SourceDatabase Cosmo, DBA Generates RESTORE tsql code for all database except [Cosmo] and [DBA] with latest Full/Diff/TLog backups from path '\\SQLBackups\ProdSrv01'. .EXAMPLE C:\PS> Script-SQLDatabaseRestore -BackupPath \\SQLBackups\ProdSrv01 -Destination_SQLInstance ProdSrv02 -DestinationPath_Data F:\mssqldata\Data -DestinationPath_Log E:\Mssqldata\Log -RestoreAs Cosmo_Temp -RestoreCategory LatestAvailable -SourceDatabase Cosmo Generates RESTORE tsql code for [Cosmo] database to be restored as [Cosmo_Temp] on destination with latest Full/Diff/TLog backup from path '\\SQLBackups\ProdSrv01'. .EXAMPLE C:\PS> Script-SQLDatabaseRestore -BackupPath \\SQLBackups\ProdSrv01 -Destination_SQLInstance ProdSrv02 -DestinationPath_Data F:\mssqldata\Data -DestinationPath_Log E:\Mssqldata\Log -RestoreCategory LatestAvailable -SourceDatabase Cosmo, DBA -StopAtTime "2018-04-12 23:15:00" Generates RESTORE tsql code for [Cosmo] and [DBA] databases upto time '2018-04-12 23:15:00' using Full/Diff/TLog backups from path '\\SQLBackups\ProdSrv01'. .LINK https://github.com/imajaydwivedi/SQLDBATools https://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/ https://youtu.be/v4r2lhIFii4 .NOTES Author: Ajay Dwivedi EMail: ajay.dwivedi2007@gmail.com Date: June 28, 2010 Documentation: https://github.com/imajaydwivedi/SQLDBATools #> [CmdletBinding()] Param ( [Parameter( Mandatory=$true )] [ValidateNotNullOrEmpty()] [Alias('Target_SQLInstance','SQLInstance_Destination')] [String]$Destination_SQLInstance = (Read-Host "Provide Destination SQL Instance name: "), [Parameter( Mandatory=$true )] [Alias('SQLInstance_Source')] [String]$Source_SQLInstance = (Read-Host "Provide Source SQL Instance name: "), [parameter( Mandatory=$false)] [Alias('ConsiderBackupsOnDisk')] [Switch]$ScanBackupsOnDisk, [Parameter( Mandatory=$true )] [ValidateNotNullOrEmpty()] [Alias('PathOfBackups')] [String]$BackupPath, [Parameter( Mandatory=$true, ParameterSetName="FromBackupHistory")] [Parameter( Mandatory=$true, ParameterSetName="RestoreAs_BackupFromHistory" )] [Alias('SQLInstance_Source')] [String]$Source_SQLInstance, [Parameter( Mandatory=$true )] [ValidateSet("LatestAvailable", "LatestFullOnly", "LatestFullAndDiffOnly","PointInTime")] [String]$RestoreCategory = "LatestAvailable", [Parameter( Mandatory=$false, HelpMessage="Enter DateTime in 24 hours format (yyyy-MM-dd hh:mm:ss)")] [String]$StopAtTime = $null, [Parameter( Mandatory=$false, ParameterSetName="RestoreAs_BackupFromPath" )] [Parameter( Mandatory=$false, ParameterSetName="RestoreAs_BackupFromHistory" )] [Alias('DirectlyExecute')] [Switch]$ExecuteDirectly_DonotScriptout, [parameter( Mandatory=$false)] [Alias('Replace')] [Switch]$Overwrite, [parameter( Mandatory=$false)] [Switch]$NoRecovery, [Parameter( Mandatory=$true)] [ValidateNotNullOrEmpty()] [Alias('Destination_Data_Path','Data_Path_Destination')] [String]$DestinationPath_Data, [Parameter( Mandatory=$true)] [ValidateNotNullOrEmpty()] [Alias('Destination_Log_Path','Log_Path_Destination')] [String]$DestinationPath_Log, [Parameter( Mandatory=$false, ParameterSetName="BackupsFromPath")] [Parameter( Mandatory=$true, ParameterSetName="RestoreAs_BackupFromPath" )] [Parameter( Mandatory=$false, ParameterSetName="FromBackupHistory")] [Parameter( Mandatory=$true, ParameterSetName="RestoreAs_BackupFromHistory" )] [Alias('SourceDatabasesToRestore')] [String[]]$SourceDatabase, [Parameter( Mandatory=$false, ParameterSetName="BackupsFromPath")] [Parameter( Mandatory=$false, ParameterSetName="FromBackupHistory")] [Switch]$Skip_Databases, [Parameter( Mandatory=$true, ParameterSetName="RestoreAs_BackupFromPath" )] [Parameter( Mandatory=$true, ParameterSetName="RestoreAs_BackupFromHistory" )] [Alias('DestinationDatabase_NewName')] [String]$RestoreAs ) # Create File for storing result $ResultFile = "C:\temp\RestoreDatabaseScripts_$(Get-Date -Format ddMMMyyyyTHHmm).sql"; if ([string]::IsNullOrEmpty($StopAtTime) -eq $false) { # StopAt in String format try { Write-Verbose "`$StopAtTime = '$StopAtTime'"; $format = "yyyy-MM-dd HH:mm:ss"; Write-Verbose "`$format = '$format'"; $StopAt_Time = [DateTime]::ParseExact($StopAtTime, $format, $null); Write-Verbose "`$StopAt_Time = '$StopAt_Time'"; $StopAt_String = ($StopAt_Time).ToString('MMM dd, yyyy hh:mm:ss tt'); Write-Verbose "`$StopAt_String = '$StopAt_String'"; } catch { Write-Error "Invalid datetime format specified for `$StopAt_Time parameter. Kindly use format: (yyyy-MM-dd hh:mm:ss)"; return; } } if ([string]::IsNullOrEmpty($StopAtTime) -eq $false -and $RestoreCategory -ne 'PointInTime') { Write-Error "Value for `$StopAtTime parameter is not required since `$RestoreCategory is not equal to 'PointInTime'"; return; } if ($RestoreCategory -eq 'PointInTime' -and [string]::IsNullOrEmpty($StopAtTime) -eq $true) { Write-Error "Value for `$StopAtTime parameter is Mandatory for `$RestoreCategory = 'PointInTime'"; return; } # Add blackslash '\' at the end of path if ($DestinationPath_Data.EndsWith('\') -eq $false) { $DestinationPath_Data += '\'; } if ($DestinationPath_Log.EndsWith('\') -eq $false) { $DestinationPath_Log += '\'; } # Final Query to execute against Destination $fileHeaders = @(); # Check if backups is to be searched from BackupPath/BackupHistory if (([String]::IsNullOrEmpty($BackupPath)) -eq $false) { Write-Verbose "Value for `$BackupPath parameter is provided. Checking its validity"; if ( (Test-Path -Path $BackupPath) -eq $false ) { Write-Error "`$BackupPath value '$BackupPath' is invalid. Pls check again."; return; } Write-Verbose "Finding all files from path:- $BackupPath"; $files = @(Get-ChildItem -Path $BackupPath -File -Recurse | Select-Object -ExpandProperty FullName); Write-Verbose "Reading Header of all the backup files found."; foreach ($bkpFile in $files) { $header = (Invoke-Sqlcmd -ServerInstance $Destination_SQLInstance -Query "restore headeronly from disk = '$bkpFile'"); if($header.BackupTypeDescription -eq 'Database') { $IsBaseBackupAvailable = $true; if ($header.IsCopyOnly -eq 0) { $IsValidForPointInTimeRecovery = $true; } else { $IsValidForPointInTimeRecovery = $false; } } else { $IsBaseBackupAvailable = $false; $IsValidForPointInTimeRecovery = $true; } $headerInfo = [Ordered]@{ 'BackupFile' = $bkpFile; 'BackupTypeDescription' = $header.BackupTypeDescription; 'ServerName' = $header.ServerName; 'UserName' = $header.UserName; 'DatabaseName' = $header.DatabaseName; 'DatabaseCreationDate' = $header.DatabaseCreationDate; 'BackupSize' = $header.BackupSize; 'FirstLSN' = $header.FirstLSN; 'LastLSN' = $header.LastLSN; 'CheckpointLSN' = $header.CheckpointLSN; 'DatabaseBackupLSN' = $header.DatabaseBackupLSN; 'BackupStartDate' = $header.BackupStartDate; 'BackupFinishDate' = $header.BackupFinishDate; 'CompatibilityLevel' = $header.CompatibilityLevel; 'Collation' = $header.Collation; 'IsCopyOnly' = $header.IsCopyOnly; 'RecoveryModel' = $header.RecoveryModel; 'NextDiffBackupFile' = $null; 'NextTLogBackupFile' = $null; 'IsValidForPointInTimeRecovery' = $IsValidForPointInTimeRecovery; 'FilePresentOnDisk' = $true; } $obj = New-Object -TypeName psobject -Property $headerInfo; $fileHeaders += $obj; } } else # Find backups from Backup History { Write-Host "Trying to read Backup History."; if($SourceDatabase.Count -gt 0) { $SourceDatabase_CommaSeparated = "'"+($SourceDatabase -join "','")+"'"; Write-Verbose "`$SourceDatabase_CommaSeparated = $SourceDatabase_CommaSeparated"; } # Find databases whose backup history is available $query_databasesFromBackupHistory = @" SET NOCOUNT ON; DECLARE @dbName VARCHAR(125), @backupStartDate datetime, @stopAtTime datetime; DECLARE @SQLString nvarchar(2000); DECLARE @ParmDefinition nvarchar(500); IF OBJECT_ID('tempdb..#BackupHistory') IS NOT NULL DROP TABLE #BackupHistory; CREATE TABLE #BackupHistory ( [BackupFile] [nvarchar](260) NULL, [BackupTypeDescription] [varchar](21) NULL, [ServerName] [char](100) NULL, [UserName] [nvarchar](128) NULL, [DatabaseName] [nvarchar](128) NULL, [DatabaseCreationDate] [datetime] NULL, [BackupSize] [numeric](20, 0) NULL, [FirstLSN] [numeric](25, 0) NULL, [LastLSN] [numeric](25, 0) NULL, [CheckpointLSN] [numeric](25, 0) NULL, [DatabaseBackupLSN] [numeric](25, 0) NULL, [BackupStartDate] [datetime] NULL, [BackupFinishDate] [datetime] NULL, [CompatibilityLevel] [tinyint] NULL, [Collation] [nvarchar](128) NULL, [IsCopyOnly] [bit] NULL, [RecoveryModel] [nvarchar](60) NULL ) ; /* Build the SQL string to get all latest backups for database. */ SET @SQLString = N'SELECT BackupFile = bmf.physical_device_name, CASE bs.type WHEN ''D'' THEN ''Database'' WHEN ''I'' THEN ''Differential database'' WHEN ''L'' THEN ''Log'' ELSE NULL END as BackupTypeDescription, LTRIM(RTRIM(CAST(SERVERPROPERTY(''ServerName'') AS VARCHAR(125)))) as ServerName, UserName = bs.user_name, bs.database_name, DatabaseCreationDate = bs.database_creation_date, BackupSize = bs.backup_size, FirstLSN = bs.first_lsn, LastLSN = bs.last_lsn, CheckpointLSN = bs.checkpoint_lsn, DatabaseBackupLSN = bs.database_backup_lsn, BackupStartDate = bs.backup_start_date, BackupFinishDate = bs.backup_finish_date, CompatibilityLevel = bs.compatibility_level, Collation = bs.collation_name, IsCopyOnly = bs.is_copy_only, RecoveryModel = bs.recovery_model FROM msdb.dbo.backupmediafamily AS bmf INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id WHERE database_name = @q_dbName AND bs.backup_start_date >= @q_backupStartDate'; SET @ParmDefinition = N'@q_dbName varchar(125), @q_backupStartDate datetime2'; DECLARE databases_cursor CURSOR LOCAL FORWARD_ONLY FOR -- Find latest Full backup for each database SELECT MAX(bs.backup_start_date) AS Latest_FullBackupDate, database_name FROM msdb.dbo.backupmediafamily AS bmf INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id WHERE bs.type='D' and is_copy_only = 0 $( if($RestoreCategory -eq 'PointInTime'){ "AND bs.backup_start_date <= '$StopAtTime'"} ) $( if($SourceDatabase.Count -gt 0){ if($Skip_Databases){"AND database_name NOT IN ($SourceDatabase_CommaSeparated)"}else{"AND database_name IN ($SourceDatabase_CommaSeparated)"} } ) GROUP BY database_name; OPEN databases_cursor FETCH NEXT FROM databases_cursor INTO @backupStartDate, @dbName; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY -- Find latest backups INSERT #BackupHistory EXECUTE sp_executesql @SQLString, @ParmDefinition, @q_dbName = @dbName, @q_backupStartDate = @backupStartDate; END TRY BEGIN CATCH PRINT ' -- ---------------------------------------------------------'; PRINT ERROR_MESSAGE(); PRINT ' -- ---------------------------------------------------------'; END CATCH FETCH NEXT FROM databases_cursor INTO @backupStartDate, @dbName; END CLOSE databases_cursor; DEALLOCATE databases_cursor ; SELECT * FROM #BackupHistory; "@; $databasesFromBackupHistory = Invoke-Sqlcmd -ServerInstance $Source_SQLInstance -Query $query_databasesFromBackupHistory; $nodes = @(Invoke-Sqlcmd -ServerInstance $Source_SQLInstance -Query 'SELECT NodeName FROM sys.dm_os_cluster_nodes;' | Select-Object -ExpandProperty NodeName); foreach ($header in $databasesFromBackupHistory) { if($header.BackupTypeDescription -eq 'Database') { $IsBaseBackupAvailable = $true; if ($header.IsCopyOnly -eq 1) { $IsValidForPointInTimeRecovery = $false; } else { $IsValidForPointInTimeRecovery = $true; } } else { $IsBaseBackupAvailable = $false; $IsValidForPointInTimeRecovery = $true; } # Check if backupFile is connecting $bkpFile = $header.BackupFile; if( ([System.IO.File]::Exists($bkpFile)) -eq $false -and $bkpFile -like "*:*") { Write-Verbose " Trying to find network path for backup file $bkpFile"; $n = "\\$($header.ServerName)\"+($bkpFile -replace ":","$"); if ([System.IO.File]::Exists($n)) { $bkpFile = $n; } elseif ($nodes.Count -gt 0) { foreach($node in $nodes) { $n = "\\$node\"+($bkpFile -replace ":","$"); if ([System.IO.File]::Exists($n)) { $bkpFile = $n; break; } } } } if ([System.IO.File]::Exists($bkpFile)) { $FilePresentOnDisk = $true; } else {$FilePresentOnDisk = $false;} $headerInfo = [Ordered]@{ 'BackupFile' = $bkpFile; 'BackupTypeDescription' = $header.BackupTypeDescription; 'ServerName' = $header.ServerName; 'UserName' = $header.UserName; 'DatabaseName' = $header.DatabaseName; 'DatabaseCreationDate' = $header.DatabaseCreationDate; 'BackupSize' = $header.BackupSize; 'FirstLSN' = $header.FirstLSN; 'LastLSN' = $header.LastLSN; 'CheckpointLSN' = $header.CheckpointLSN; 'DatabaseBackupLSN' = $header.DatabaseBackupLSN; 'BackupStartDate' = $header.BackupStartDate; 'BackupFinishDate' = $header.BackupFinishDate; 'CompatibilityLevel' = $header.CompatibilityLevel; 'Collation' = $header.Collation; 'IsCopyOnly' = $header.IsCopyOnly; 'RecoveryModel' = $header.RecoveryModel; 'NextDiffBackupFile' = $null; 'NextTLogBackupFile' = $null; 'IsValidForPointInTimeRecovery' = $IsValidForPointInTimeRecovery; 'FilePresentOnDisk' = $FilePresentOnDisk; } $obj = New-Object -TypeName psobject -Property $headerInfo; $fileHeaders += $obj; } } if (@($fileHeaders | Where-Object {$_.FilePresentOnDisk -eq $false}).Count -gt 0) { " Below backup files are missing from disk:- " | Write-Host -ForegroundColor DarkRed -BackgroundColor Yellow; $fileHeaders | Where-Object {$_.FilePresentOnDisk -eq $false} | Select-Object DatabaseName, BackupTypeDescription, BackupFile | Write-Host -ForegroundColor Magenta -BackgroundColor Yellow; } Write-Verbose "Removing system databases, and backup files not present on disk."; $fileHeaders = ($fileHeaders | Where-Object {@('master','model','msdb') -notcontains $_.DatabaseName -and $_.FilePresentOnDisk -eq $true} | Sort-Object -Property DatabaseName, BackupStartDate); Write-Verbose "Updating value for additional fields like NextDiffBackupFile and NextTLogBackupFile"; $databases = @($fileHeaders | Select-Object DatabaseName -Unique | Select-Object -ExpandProperty DatabaseName); foreach ($dbName in $databases) { Write-Verbose " Lopping for Database [$dbName]"; #Fetch all backups for database $dbBackups = $fileHeaders | Where-Object {$_.DatabaseName -eq $dbName} | Sort-Object BackupStartDate, BackupFinishDate; #Loop through each backup file, and update those additional fields foreach ($file in $dbBackups) { # if current file is Full Backup if ($file.BackupTypeDescription -eq 'Database' -and $file.IsValidForPointInTimeRecovery) { # Diff.DatabaseBackupLSN = Full.CheckpointLSN $NextDiffBackupFile = $dbBackups | Where-Object {$_.BackupTypeDescription -eq 'Differential database' -and $_.DatabaseBackupLSN -eq $file.CheckpointLSN} | Sort-Object BackupStartDate -Descending | Select-Object -ExpandProperty BackupFile -First 1; if ($NextDiffBackupFile -eq $null) { Write-Verbose " No applicable Differential backups found."; } else { Write-Verbose " `$NextDiffBackupFile = $NextDiffBackupFile"; } # Full.LastLSN + 1 between TLog.FirstLSN and TLog.LastLSN $FullLastLSN = $file.LastLSN + 1; $NextTLogBackupFile = $dbBackups | Where-Object {$_.BackupTypeDescription -eq 'Log' -and ( $FullLastLSN -ge $_.FirstLSN -and $FullLastLSN -le $_.LastLSN )} | Select-Object -ExpandProperty BackupFile; if ($NextTLogBackupFile -eq $null) { Write-Verbose " No applicable Transaction log backups found."; } } # if current file is Diff Backup if ($file.BackupTypeDescription -eq 'Differential database') { # Full.LastLSN + 1 between TLog.FirstLSN and TLog.LastLSN $DiffLastLSN = $file.LastLSN + 1; $NextTLogBackupFile = $dbBackups | Where-Object {$_.BackupTypeDescription -eq 'Log' -and ( $DiffLastLSN -ge $_.FirstLSN -and $DiffLastLSN -le $_.LastLSN )} | Select-Object -ExpandProperty BackupFile; if ($NextTLogBackupFile -eq $null) { Write-Verbose " No applicable Transaction log backups found."; } } if ([String]::IsNullOrEmpty($NextDiffBackupFile) -eq $false) { $file.NextDiffBackupFile = $NextDiffBackupFile; $file.NextTLogBackupFile = $NextDiffBackupFile; } if ([String]::IsNullOrEmpty($NextTLogBackupFile) -eq $false) { $file.NextTLogBackupFile = $NextTLogBackupFile; $file.NextTLogBackupFile = $NextTLogBackupFile; } } } Write-Verbose "Checking if specific source databases are provided"; if ([String]::IsNullOrEmpty($SourceDatabase) -eq $false) { # if 'RestoreAs_BackupFromPath' or 'RestoreAs_BackupFromHistory' options are selected if( [String]::IsNullOrEmpty($RestoreAs) -eq $false -and $SourceDatabase.Count -ne 1) { Write-Error "Kindly provide only single database name for parameter `$SourceDatabase"; return; } Write-Verbose "Checking if databases are to be Skipped."; if ($Skip_Databases) { Write-Verbose "Skip_Databases is set to TRUE"; Write-Verbose "Removing databases based on parameter value `$SourceDatabase"; $fileHeaders = ($fileHeaders | Where-Object {$SourceDatabase -notcontains $_.DatabaseName} | Sort-Object -Property DatabaseName, BackupStartDate); } else { Write-Verbose "Filtering databases based on parameter value `$SourceDatabase"; $fileHeaders = ($fileHeaders | Where-Object {$SourceDatabase -contains $_.DatabaseName} | Sort-Object -Property DatabaseName, BackupStartDate); } } Write-Verbose "Perform action based on value of `$RestoreCategory parameter"; if ($RestoreCategory -eq 'LatestAvailable') { # No action to be taken for RestoreCategory = "LatestAvailable" Write-Verbose "Filtering backups based on `$RestoreCategory = '$RestoreCategory'"; } elseif ($RestoreCategory -eq 'LatestFullOnly') { Write-Verbose "Filtering backups based on `$RestoreCategory = '$RestoreCategory'"; $fileHeaders = ($fileHeaders | Where-Object {$_.BackupTypeDescription -eq 'Database'} | Sort-Object -Property DatabaseName, BackupStartDate -Descending); } elseif ($RestoreCategory -eq 'LatestFullAndDiffOnly') #"LatestFullOnly", "LatestFullAndDiffOnly","PointInTime" { Write-Verbose "Filtering backups based on `$RestoreCategory = '$RestoreCategory'"; $fileHeaders = ($fileHeaders | Where-Object {$_.BackupTypeDescription -eq 'Database' -or $_.BackupTypeDescription -eq 'DATABASE DIFFERENTIAL'} | Sort-Object -Property DatabaseName, BackupStartDate); } elseif ($RestoreCategory -eq 'PointInTime') { $databases = @($fileHeaders | Select-Object DatabaseName -Unique | Select-Object -ExpandProperty DatabaseName); Write-Verbose "Filtering backups based on `$RestoreCategory = '$RestoreCategory'"; $pit_Backups_FullorDiff = @(); $pit_Backups_TLog = @(); # get full and diff backups upto @StopAtTime $pit_Backups_FullorDiff = ($fileHeaders | Where-Object {'DATABASE','DATABASE DIFFERENTIAL' -contains $_.BackupTypeDescription -and $_.BackupStartDate -le $StopAt_Time} | Sort-Object -Property DatabaseName, BackupStartDate); # get tlog backups to recover for @StopAtTime foreach ( $dbName in $databases ) { $pit_LastTLogStartDate = $null; $pit_LastTLogStartDate = ($fileHeaders | Where-Object {$dbName -eq $_.DatabaseName -and $_.BackupTypeDescription -eq 'TRANSACTION LOG' -and $_.BackupStartDate -ge $StopAt_Time} | Measure-Object -Property BackupStartDate -Minimum).Minimum; $pit_Backups_TLog += ($fileHeaders | Where-Object {$dbName -eq $_.DatabaseName -and $_.BackupTypeDescription -eq 'TRANSACTION LOG' -and $_.BackupStartDate -le $pit_LastTLogStartDate} | Sort-Object -Property DatabaseName, BackupStartDate); } $fileHeaders = $pit_Backups_FullorDiff; $fileHeaders += $pit_Backups_TLog; $fileHeaders = $fileHeaders | Sort-Object DatabaseName, BackupStartDate; } $latestBackups = @(); $latestBackups_Full = @(); $latestBackups_Diff = @(); $latestBackups_TLog = @(); # reset names of $databases $databases = @($fileHeaders | Select-Object DatabaseName -Unique | Select-Object -ExpandProperty DatabaseName); foreach ( $dbName in $databases ) { $lastestFullBackupDate = $null; # reset variable value $lastestDiffBackupDate = $null; # reset variable value $fullBackupHeader = $null; # reset variable value # Get Full Backup details $lastestFullBackupDate = ($fileHeaders | Where-Object {$dbName -eq $_.DatabaseName -and $_.BackupTypeDescription -eq 'Database'} | Measure-Object -Property BackupStartDate -Maximum).Maximum; $fullBackupHeader = ($fileHeaders | Where-Object {$dbName -eq $_.DatabaseName -and $_.BackupTypeDescription -eq 'Database' -and $_.BackupStartDate -eq $lastestFullBackupDate}); $latestBackups_Full += $fullBackupHeader; if ($fullBackupHeader.IsCopyOnly -eq 1) { Write-Verbose "Latest Full Backup of database [$dbName] is COPY_ONLY"; } else { # Get Diff Backup details on top of Full Backup if ($lastestFullBackupDate -ne $null) { $lastestDiffBackupDate = ($fileHeaders | Where-Object {$dbName -eq $_.DatabaseName -and $_.BackupTypeDescription -eq 'DATABASE DIFFERENTIAL' -and $_.BackupStartDate -ge $lastestFullBackupDate} | Measure-Object -Property BackupStartDate -Maximum).Maximum; $latestBackups_Diff += ($fileHeaders | Where-Object {$dbName -eq $_.DatabaseName -and $_.BackupTypeDescription -eq 'DATABASE DIFFERENTIAL' -and $_.BackupStartDate -eq $lastestDiffBackupDate}); } if ($fullBackupHeader.RecoveryModel -ne 'SIMPLE') { if ($lastestDiffBackupDate -eq $null) { $lastestDiffBackupDate = $lastestFullBackupDate; } # Get TLog Backup details on top of Differential Backup if ($lastestDiffBackupDate -ne $null) { $latestBackups_TLog += ($fileHeaders | Where-Object {$dbName -eq $_.DatabaseName -and $_.BackupTypeDescription -eq 'Log' -and $_.BackupStartDate -ge $lastestDiffBackupDate}); } } } } $latestBackups += $latestBackups_Full; $latestBackups += $latestBackups_Diff; $latestBackups += $latestBackups_TLog; #$filelistFromBackupFiles = @(); [int]$fileCounter_Total = 1; [int]$fileCounter_Database = 1; Write-Verbose "Looping through all the databases one by one to generate RESTORE statement."; foreach ( $dbName in $databases ) { $fileCounter_Database = 1; if([String]::IsNullOrEmpty($RestoreAs) -eq $false) { $dbName_New = $RestoreAs; }else { $dbName_New = $dbName; }; Write-Verbose " Generating RESTORE statement for database [$dbName]"; $backupFilesForDatabase = $latestBackups | Where-Object {$dbName -eq $_.DatabaseName} | Sort-Object BackupStartDate; $bkpCountsForDB = @($backupFilesForDatabase).Count; Write-Verbose " `$bkpCountsForDB for [$dbName] database is $bkpCountsForDB"; $tsql4Database = $null; $tsql4Database = @" PRINT '$fileCounter_Total) Restoring database [$dbName_New]' "@; foreach ($file in $backupFilesForDatabase) { $tsql4Database += @" PRINT ' File no: $fileCounter_Database' RESTORE DATABASE [$dbName_New] FROM DISK ='$($file.BackupFile)' WITH "@; Write-Verbose " Reading filelist for file '$($file.BackupFile)'"; $query = "restore filelistonly from disk = '$($file.BackupFile)'"; $list = Invoke-Sqlcmd -ServerInstance $Destination_SQLInstance -Query $query; $list = ($list | Sort-Object FileId); # If differtial or TLog, with MOVE option is not required if($fileCounter_Database -eq 1) { foreach ($f in $list) { $physicalName = $f.PhysicalName; #F:\Mssqldata\Data\UserTracking_data.mdf $r = $physicalName -match "^(?'PathPhysicalName'.*[\\\/])(?'BasePhysicalNameWithExtension'(?'BasePhysicalNameWithoutExtension'.+)(?'Extension'\.[a-zA-Z]+$))"; $BasePhysicalNameWithExtension = $Matches['BasePhysicalNameWithExtension']; #UserTracking_data.mdf $Extension = $Matches['Extension']; #.mdf $PathPhysicalName = $Matches['PathPhysicalName']; #F:\Mssqldata\Data\ $BasePhysicalNameWithoutExtension = $Matches['BasePhysicalNameWithoutExtension']; #UserTracking_data # When database has to be restored with New Name. Then change physicalName of files if ([String]::IsNullOrEmpty($RestoreAs) -eq $false) { $BasePhysicalNameWithExtension_New = $BasePhysicalNameWithExtension.Replace("$dbName","$RestoreAs"); if($BasePhysicalNameWithExtension_New -eq $BasePhysicalNameWithExtension) { $BasePhysicalNameWithExtension_New = $RestoreAs +'_'+ $BasePhysicalNameWithExtension_New; } } else { $BasePhysicalNameWithExtension_New = $BasePhysicalNameWithExtension; } if ($f.Type -eq 'D') { $PhysicalPath_New = $DestinationPath_Data + $BasePhysicalNameWithExtension_New; } else { $PhysicalPath_New = $DestinationPath_Log + $BasePhysicalNameWithExtension_New; } $tsql4Database += @" MOVE '$($f.LogicalName)' TO '$PhysicalPath_New', "@; } } #$tsql4BackupFile += $tsql4MoveCommand; #if its last backup file to apply if ($bkpCountsForDB -eq $fileCounter_Database) { $tsql4Database += @" $(if($Overwrite -and $fileCounter_Database -eq 1){'REPLACE, '})$(if($NoRecovery){'NORECOVERY, '})$(if($fileCounter_Database -ne 1 -and $RestoreCategory -eq 'PointInTime'){'STOPAT = '''+$StopAt_String+''', '})STATS = 3 GO "@; } else { $tsql4Database += @" $(if($Overwrite -and $fileCounter_Database -eq 1){'REPLACE, '})NORECOVERY, STATS = 3 GO "@; } $fileCounter_Database += 1; } $tsql4Database | Out-File -Append $ResultFile; $fileCounter_Total += 1; } Write-Host "Opening generated script file '$ResultFile' with SSMS."; if ($Destination_SQLInstance -eq $null) { $Destination_SQLInstance = $sdtInventoryInstance; } if (Test-Path $ResultFile) { #ssms.exe $ResultFile -S $Destination_SQLInstance -E; notepad.exe $ResultFile; } else { Write-Host "No valid backup files found for Restore Activity.."; } } |