$databaseName='BALOO_MSCRM'
$newDatabaseName='BALOO_MSCRM'
$dbData='mscrm' # set this value to $null for autogen defaults
$dbLog='mscrm_log' # set this value to $null for autogen defaults
function restoreDatabase{
param(
$databaseName,
$dbData,
$dbLog,
$newDatabaseName
)
if(!$dbData){$dbData=$databaseName}
if(!$dbLog){$dbData=$databaseName+'_log'}
if(!$newDatabaseName){$newDatabaseName=$databaseName}
function pickIndex($list){
function displayList($list){
for ($i=0;$i -lt $list.count;$i++){
write-host "$i`: $(($list[$i]|out-string).trim())"
}
}
do {
displayList $list
try {
$flag = $true
[int]$pick=Read-Host -Prompt "`n--------------------------------------------------------`nPlease type the number corresponding to the desired item`n--------------------------------------------------------"
} # end try
catch {$flag = $false}
} until ($pick -lt $list.count -and $flag)
$pickIndex=$pick
$pickedItem=$list[$pick]
write-host "Picked index is $pickIndex, which corresponds to: $pickedItem"
return $pickIndex
}
function confirmation($content,$testValue="I confirm",$maxAttempts=3){
$confirmed=$false;
$attempts=0;
$content|write-host
write-host "Please review this content for accuracy.`r`n"
while ($attempts -le $maxAttempts){
if($attempts++ -ge $maxAttempts){
write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
break;
}
$userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
if ($userInput.ToLower() -eq $testValue.ToLower()){
$confirmed=$true;
write-host "Confirmed!`r`n";
break;
}elseif($userInput -like 'cancel'){
write-host 'Cancel command received.'
$confirmed=$false
break
}else{
Clear-Host;
write-host $($content|out-string).Trim()
write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
}
}
return $confirmed;
}
<# SQLSERVER module is not being used in this iteration because the Get-SqlBackupHistory does not show backup file locations
if(!(get-module sqlserver -ea Silentlycontinue)){
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
#The Tls12 setting preempts this error:
#WARNING: Unable to resolve package source 'https://www.powershellgallery.com/api/v2'.
#PackageManagement\Install-Package : No match was found for the specified search criteria and module name 'sqlserver'.
#Try Get-PSRepository to see all available registered module repositories.
#At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:1772 char:21
#+ ... $null = PackageManagement\Install-Package @PSBoundParameters
#+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# + CategoryInfo : ObjectNotFound: (Microsoft.Power....InstallPackage:InstallPackage) [Install-Package], Exception
# + FullyQualifiedErrorId : NoMatchFoundForCriteria,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackage
install-module sqlserver -allowclobber -force
}
$backups=Get-SqlBackupHistory -ServerInstance $env:computername -DatabaseName $databaseName
$backupDates=$backups.BackupStartDate
$pickIndex=pickIndex $backupDates
$backupItem=$backups[$pickIndex]
#>
import-module sqlps
$getBackups="-- Get Backup History for required database
USE $databaseName
GO
SELECT TOP 100
s.database_name,
m.physical_device_name AS location,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date ASC, backup_finish_date
GO
"
$backups=invoke-sqlcmd $getBackups
$backupDates=$backups.backup_start_date
$pickIndex=pickIndex $backupDates
$backupItem=$backups[$pickIndex]
$backupFile=$backupItem.location
#This SQLSERVER module cmdlet is not being used
#Restore-SqlDatabase -ServerInstance "." -Database $databaseName -BackupFile $backupItem.location
<# Some errors that have to do with DB being in use. Also, WITHNORECOVERY option must specified for FULL restores.
Restore-SqlDatabase : System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no
files are ready to rollforward.
At line:1 char:1
+ Restore-SqlDatabase -ServerInstance "." -Database $databaseName -Back ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Restore-SqlDatabase], SmoException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand
Restore-SqlDatabase : System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.
At line:1 char:1
+ Restore-SqlDatabase -ServerInstance "." -Database $databaseName -Back ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Restore-SqlDatabase], SmoException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand
#>
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername
$defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
$defaultDataDirectory=$sqlConnection.Settings.DefaultFile
$defaultLogDirectory=$sqlConnection.Settings.DefaultLog
$sqlDefaults=@{
'dataDirectory'=$defaultDataDirectory
'logDirectory'=$defaultLogDirectory
'backupDirectory'=$defaultBackupDirectory
}
$backupFileIsComplete=if($backupFile -match 'bak$'){$true}else{$false}
if($backupFileIsComplete){
$restoreDatabase="
USE master
GO
ALTER DATABASE $databaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [$databaseName] FROM DISK = N'$backupFile' WITH FILE = 1,
MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
NOUNLOAD, REPLACE, STATS = 5;
GO
"
}else{
$lastFullBackup=.{
$backupLocations=$backups.location
$resultFound=$false
$currentIndex=$pickIndex-1
Do{
$currentIndex-=1
if($backupLocations[$currentIndex] -match 'bak$'){
$resultFound=$true
return $backupLocations[$currentIndex]
}
}until($resultFound)
}
$restoreDatabase="
USE master
GO
ALTER DATABASE $databaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [$databaseName] FROM DISK = N'$lastFullBackup' WITH NORECOVERY,
MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
NOUNLOAD, REPLACE, STATS = 5;
GO
RESTORE DATABASE [$databaseName] FROM DISK = N'$backupFile' WITH RECOVERY,
MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
NOUNLOAD, REPLACE, STATS = 5;
GO
"
}
$confirmed=confirmation -content $restoreDatabase
if($confirmed){
try{
invoke-sqlcmd -Query $restoreDatabase -QueryTimeout $([int]::MaxValue) -ConnectionTimeout 0 -Verbose
return $true
}catch{
write-warning $_
return $false
}
}else{
write-host 'No confirmations received. No changes have been made.'
return $false
}
}
restoreDatabase $databaseName $dbData $dbLog $newDatabaseName
September 28, 2020September 28, 2020
0 Comments