Timeouts in SqlPS Backup-SqlDatabase & Restore-SqlDatabase

Often when backing up a big database using SqlPS’s Backup-SqlDatabase cmdlet you’ll find yourself hitting a timeout after 600 seconds (10 minutes).  In order to alleviate this, you’ll need to pass in an SMO Server object to the Backup-SqlDatabase instead of defining the –ServerInstance:

try{

    $srv = new-object Microsoft.SqlServer.Management.Smo.Server "MyServerName"
    $srv.ConnectionContext.StatementTimeout = 0
    Backup-SqlDatabase -InputObject $srv -Database "MyBigDatabase" -BackupAction Database -BackupFile "f:\Backups\MyBigDatabase.bak" -CopyOnly -CompressionOption On 

}
catch{
    $_ | fl -Force
}

The same trick *should* work (as I’ve not tried it yet) via the Restore-SqlDatabase, as it also takes an –InputObject of type smo server as well.  This was supposedly fixed in Sql Server 2012 SP2, but if you have just the SqlCLR, SharedManagement, & PowerShell tools installed installed sans sql server, it can be an un-necessary pain to have to apply a service pack just to fix this issue.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: