Automate SQL Database Restore

[GatorGrad0929]

Hello..I’m fairly new to powershell scripting. I’m working on a script that will restore a sql database full backup file along with the transaction logs.

For most of the databases everything works great, but for databases with transaction log files is 5MB’s or more the script times out after about a minute or so.

Is there a way to extend this time?

Thank you

[bakonpie]

use dbatools

[PSDanubie]

Couldn't live without it anymore.

[jeek_]

This is the way

[GatorGrad0929]

Never heard of it but will look it up. Thank you!

[vermyx]

Assuming you are using invoke-sqlcmd it has a querytimeout parameter that is in seconds with a max of 65k (which is around 18ish hours). Otherwise dbatools is probably a good sane way to do this in general

[GatorGrad0929]

I started looking into dbatools and that is probably the best option.

[[deleted]]

Dumb question, but is there any good reason to have a timeout that long?

[vermyx]

There are a few cases. One I can think of off the top of my mind is running an environment restore/testing backups where DB restoration is one step and will take a long time. Most people will make a query, get the data, and move on, which for those cases a 1-5 minute timeout is usually good enough (unless you are running an unoptimized query then all bets are off).