Timeout problem in Backup-SQLDatabase cmdlet in SQLPS module

Power Shell is for managing all types of technologies be it SQL, EXchange, Lync, Office etc.( just name it)

These technologies usually create there own cmdlets, and then bundle into a "Module" for distribution.
SQL team initially followed different approach by creating mini-shell "sqlps.exe" but it was not well accepted.
Now, they give there cmdlets in  SQLPS module.

"Import-Module SQLPS" command loads this module.

There are multiple cmdlets available for multiple tasks like querying, backup/restore, security management, policy management etc. . Two cmdlets for taking backup/restore are :

• Backup-SQLDatabase
• Restore-SQLDatabase

These two use SMO(Server Management Objects) classes underneath and then run T-SQL commands.

Backup-SqlDatabase  is a wrapper over SMO object model (managed code); SMO constructs T-SQL code and executes query using ADO.NET.

SMO classes have been available for a long time, and are widely used in automation.

There are different ways to perform backup/restore operations as specified here.

If we use Power Shell to do automation of this activity ( which we should ), we can use the most simple cmdlets to use aka Backup-SQLDatabase and Restore-SQLDatabase.

However, both these have one serious bug i.e. if a backup or restore takes more than 10 minutes, they time-out and fail.
Usually, backup/restore of large databases take more than 10 minutes to complete (which is why we wanted to automate this).

Here's the error you will see if you run this command :
Backup-SqlDatabase -ServerInstance $Server -Database $DatabaseName -BackupFile $BackUpFile -CompressionOption On -ConnectionTimeout 0 -Initialize -Verbose -ea Stop

Here’s the error exactly after 600 seconds of execution :

VERBOSE: 60 percent processed.

VERBOSE: The backup or restore was aborted.

The wait operation timed out

    + CategoryInfo          : InvalidOperation: (:) [Backup-SqlDatabase], Win3

   2Exception

    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.P

   owerShell.BackupSqlDatabaseCommand

    + PSComputerName        : localhost 

This is a very weird issue.

You can try re configuring “remote query timeout” to 0 as given here, but the issue persists.

ConnectionTimeout switch is different than StatementTimeout. This switch is not available in these cmdlets.

The workaround lies in setting this property by connecting to SMO server.

$serverConn = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $server

$serverConn.ConnectionContext.StatementTimeout = 0 

So, I have to pass SMO.Server object to this cmdlet.

Backup-SqlDatabase -InputObject $serverConn -Database abc -BackupFile "L:\123\abc.bak" 

This will run fine for command that take longer than 600 seconds.( Default value of StatementTimeout is 600, which caused the issue).

To tell Microsoft about this, I've logged a bug in their "Connect" program.

https://connect.microsoft.com/SQLServer/feedback/details/805087/timeout-problem-in-backup-sqldatabase-cmdlet-in-sqlps-module

Please vote-up.

PS: If Power Shell is giving you some issues, don't worry. Invest time in learning and sharing. Overall, your time will be saved.

Tip : I came around this workaround after using the best cmdlets of Power Shell : Get-Help, Get-Member.

These are your base including Get-Command.

Cheers!

Why this blog ?

“It is every man's obligation to put back into the world at least the equivalent of what he takes out of it.” - Albert Einstein

There are two purposes of starting this one :

• Spread Knowledge
• Personal Diary

Spread Knowledge

Every day in day out, you Google about something that you need and hopefully find what you need. In thanks to them, I'm also sharing my experiences. I want to share what I know so that it may save someone's time. And, the other benefit of sharing is the more you tell the more you get to know.

Personal Diary

I started with PS from Jan 2013 (had some fun admiring it from July 2012), did learn many things, worked and then forgot! Since, I had to work on some other technologies as needed. But, PS lived in my heart. After some gap, I wanted to refresh. I did not have any notes. So, here I am preparing my notes. Maybe later they will prove beneficial to me.

Let's start sharing!