I have a Powershell script based on this example that I've been using to automate my database restores, which has worked without a problem for a while now.
Recently it started failing and throwing timeout errors.
To highlight the code block from that example:
############################################################################
# Restore db Script block
############################################################################
[ScriptBlock] $global:RestoreDBSMO = {
param([string] $newDBName, [string] $backupFilePath, [bool] $isNetworkPath = $true, [string] $newDataPath, [string] $newLogPath)
# http://www.codeproject.com/Articles/110908/SQL-DB-Restore-using-PowerShell
try
{
# Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
# Create sql server object
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
# Copy database locally if backup file is on a network share
if($isNetworkPath)
{
$fileName = [IO.Path]::GetFileName($backupFilePath)
$localPath = Join-Path -Path $server.DefaultFile -ChildPath $fileName
Copy-Item $backupFilePath $localPath
$backupFilePath = $localPath
}
# Create restore object and specify its settings
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
$smoRestore.Database = $newDBName
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"
# Create location to restore from
$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFilePath, "File")
$smoRestore.Devices.Add($backupDevice)
# Give empty string a nice name
$empty = ""
# Specify new data file (mdf)
$smoRestoreDataFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
if (($newDataPath -eq $null) -or ($newDataPath -eq $empty)) { #use exixting path
$defaultData = $server.DefaultFile
if (($defaultData -eq $null) -or ($defaultData -eq $empty))
{
$defaultData = $server.MasterDBPath
}
} Else {$defaultData = $newDataPath}
$fullPath = Join-Path -Path $defaultData -ChildPath ($newDBName + "_Data.mdf")
$smoRestoreDataFile.PhysicalFileName = $fullPath
# Specify new log file (ldf)
$smoRestoreLogFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
if (($newLogPath -eq $null) -or ($newLogPath -eq $empty)) { #use exixting path
$defaultLog = $server.DefaultLog
if (($defaultLog -eq $null) -or ($defaultLog -eq $empty))
{
$defaultLog = $server.MasterDBLogPath
}
} Else {$defaultLog = $newLogPath}
$fullPath = Join-Path -Path $defaultLog -ChildPath ($newDBName + "_Log.ldf")
$smoRestoreLogFile.PhysicalFileName = $fullPath
# Get the file list from backup file
$dbFileList = $smoRestore.ReadFileList($server)
# The logical file names should be the logical filename stored in the backup media
$smoRestoreDataFile.LogicalFileName = $dbFileList.Select("Type = 'D'")[0].LogicalName
$smoRestoreLogFile.LogicalFileName = $dbFileList.Select("Type = 'L'")[0].LogicalName
# Add the new data and log files to relocate to
$smoRestore.RelocateFiles.Add($smoRestoreDataFile)
$smoRestore.RelocateFiles.Add($smoRestoreLogFile)
# Restore the database
$smoRestore.SqlRestore($server)
"Database restore completed successfully"
}
catch [Exception]
{
"Database restore failed:`n`n " + $_.Exception
}
finally
{
# Clean up copied backup file after restore completes successfully
if($isNetworkPath)
{
Remove-Item $backupFilePath
}
}
}
When doing the actual restore, it throws the following error:
Restoring DB...
0
1
Database restore failed:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'SRV_MIC_DEV'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.
Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The backup or restore was aborted.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at SqlRestore(Object , Object[] )
at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformation methodInformation, Object[] originalArguments)
I've tried having a look over the Powershell documentation and other links and noticed that some websites mentioned that Powershell cmdlets have a 600 second default timeout.
I'm trying to find either a solution to increase this timeout from Powershell or from SQL Server, but I'm not really having any luck with documentation or regular web-searches.
Is there any quick fix for my script to increase the timeout or a simple alternative?
Any help is much appreciated.
