July 5, 2016

Subscribe

Timeouts in SqlPS Backup-SqlDatabase & Restore-SqlDatabase

by Scott Newman

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:

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.