Timeout trying to backup a db

Question

Timeout trying to backup a db

rnunez

Right there with Babe

Points: 762
More actions
November 3, 2009 at 7:28 am

#213173

Hi,
I am trying to run a script in Powershell to backup a database but everytime I attempt it, I get the following timeout error:
Invoke-Sqlcmd : Timeout expired. The timeout period elapsed prior to completio
n of the operation or the server is not responding.
The backup or restore was aborted.
At line:1 char:14
+ Invoke-SqlCmd <<<< -ServerInstance "localhost" -Database "dbname" -Query "BA
CKUP DATABASE [dbname] TO DISK='..file path'"
Here is the command I am using:
PS C:\testnewclient> Invoke-SqlCmd -ServerInstance "localhost" -Database "dbname
" -Query "BACKUP DATABASE [dbname] TO DISK=file path"
I am able to run the same backup command fine in sqlcmd in the DOS prompt and I have increased the timeout on the SQL Server using "sp_configure 'remote login timeout', 60
go
reconfigure with override
go "
Any suggestions?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Try setting the -QueryTimeout parameter on Invoke-SqlCmd .

Fiddling with 'remote login timeout' etc isn't going to help here, it's probably logging in quickly enough, it's just the actual query (BACKUP...) that is timing out.

rnunez Right there with Babe Points: 762 More actions · Answer 2

Thanks Ian, that seemed to work!

PS - Go Thunderbirds!:-D

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 3

Out of curiosity, how much timeout did you set for how many GB of data?

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

rnunez (11/3/2009)
PS - Go Thunderbirds!:-D

Wrong program... the avatar is actually Captain Scarlet (but Thunderbirds was much better):-)

Gary Varga SSC Guru Points: 82166 More actions · Answer 5

Is it safe to say that he wasn't referring to Steve's "Avatar"???

_{(I am guessing by the smile that he won't get cross!!!)}

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

rnunez Right there with Babe Points: 762 More actions · Answer 6

Hi Steve,

I just set it at random to 60s to backup 500MB of a db.

Ian, yes I was referring to your Avatar. 🙂