March 24, 2015 at 9:06 am
I have a script that I created a powershell script based on a script I found, [Start-Migration][1]. It is a great script and gave me a lot of really good ideas; however I am running into some issues when attempting to restore large databases or databases that take longer than 10 minutes. I have attempted to use both them invoke-sqlcmd2 function I found and the class for the restore for the microsoft.sqlserver.management.smo namespace. both of which are timing out after 10 minutes. I have also tried increasing the connection timeout even setting the connection to 1200. any suggestions would be welcomed.
Function Restore-SQLDatabase {
<#
.SYNOPSIS
Restores .bak file to SQL database. Creates db if it doesn't exist. $filestructure is
a custom object that contains logical and physical file locations.
.EXAMPLE
$filestructure = Get-SQLFileStructures $sourceserver $destserver $ReuseFolderstructure
Restore-SQLDatabase $destserver $dbname $backupfile $filestructure
.OUTPUTS
$true if success
$true if failure
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$server,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$dbname,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$backupfile,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$filestructure
)
$servername = $server.Name
$server.ConnectionContext.StatementTimeout = 0
$restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"
foreach($file in $filestructure.databases[$dbname].destination.values) {
$movefile = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile"
$movefile.LogicalFileName = $file.logical
$movefile.PhysicalFileName = $file.physical
$null = $restore.RelocateFiles.Add($movefile)
}
Write-Host "Restoring $dbname to $servername" -ForegroundColor Yellow
try{
$Percent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler]{
Write-Progress -Id 1 -Activity "Restoring $dbname to $ServerName" -PercentComplete $_.Percent -Status ([System.String]::Format("Progress: {0}%",$_.Percent))
}
$restore.add_PercentComplete($Percent)
$restore.PercentCompleteNotification = 1
$restore.add_Complete($Complete)
$restore.ReplaceDatabase = $true
$restore.Database = $dbname
$restore.Action = "Database"
$restore.NoRecovery = $false
$device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem
$device.Name = $backupfile
$device.DeviceType = "File"
$restore.Devices.Add($device)
Write-Progress -Id 1 -Activity "Restoring $dbname to $servername" -PercentComplete 0 -Status([System.String]::Format("Progress: {0}%",0))
$restore.SqlRestore($servername)
# $query = $restore.Script($ServerName)
# Write-Host $query
# Invoke-Sqlcmd2 -ServerInstance $servername -Database master -Query $query -ConnectionTimeout 1200
# Write-Host "Restoring $dbname to $servername from " $restore.Devices.ToString() -ForegroundColor Magenta
Write-Progress -Id 1 -Activity "Restore $dbname to $servername" -Status "Complete" -Completed
return $true
}
catch{
Write-Error $_.Exception.ToString()
Write-Warning "Restore failed: $($_.Exception.InnerException.Message)"
return $false
}
when the restore process takes place ,$restore.SqlRestore($ServerName), on my larger databases it returns saying that the script timed out. I am trying to figure out how to correct this. I have tried increasing the statementtimeout = 1200 and it still stops after 10 minutes. i even attempted to us an invoke-sqlcmd As you can see I commented it out when trying different options. I am at wits end right now.
March 26, 2015 at 3:41 pm
There is a remote query timeout option that is set to 10 minutes by default within SQL Server. This could be the problem. Unless by connection timeout you're referring to this option.
Edit: Why not just do this in SQL?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply