SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Powershell Restore Script


Powershell Restore Script

Author
Message
mitzyturbo
mitzyturbo
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 Visits: 579
Hi folks

I'm trying to adapt the powershell script at [url=http://sysadmingrunt.blogspot.com/][/url] into my own environment
The modifications I made to the original code was to pass a *.sqb in the end of the identified filename for each database backup (I have 4 sqb files per database backup). The original script was built to handle one backup file per database.

The script is working, it does restore the databases but errors are being thrown in the redgaterestore function:

Error message:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Timeout expired. Th
e timeout period elapsed prior to completion of the operation or the server is
not responding."
At T:\restoresp.ps1:87 char:32
+ $SQLCommand.ExecuteNonQuery <<<< () | Out-Null
+ CategoryInfo : NotSpecified: (Smile [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

Exception calling "ExecuteNonQuery" with "0" argument(s): "Timeout expired. Th
e timeout period elapsed prior to completion of the operation or the server is
not responding."
At T:\restoresp.ps1:87 char:32
+ $SQLCommand.ExecuteNonQuery <<<< () | Out-Null
+ CategoryInfo : NotSpecified: (Smile [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

And when running this script as part of a SQL job, the job itself fails as this error is thrown.

Any help would be greatly appreciated
Attachments
restoresp.txt (68 views, 7.00 KB)
AllenMWhite
AllenMWhite
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 154
I'd modify your script to write out the query you're sending to SQL Server to the console, then run that query in a query window in SSMS to see what the error is, but I'm guessing the syntax for handling multiple files is incorrect.



mitzyturbo
mitzyturbo
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 Visits: 579
That was my first approach alright
But if the SQL is wrong I wouldn't have expected the databases to restore.
Nicholas Cain
Nicholas Cain
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7536 Visits: 6200
What version of SQL Server are you running?



Shamless self promotion - read my blog http://sirsql.net
AllenMWhite
AllenMWhite
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 154
OK, while I don't use the redgate restore tool, the ExecuteNonQuery method is an ADO.Net method to send a query to SQL Server. The next thing I'd do is run Profiler and see what query is being sent from your script that's timing out, if the restore itself is successful.



bruce 1565
bruce 1565
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 1031
If the restore is happening the only thing left is the ChageDBOwner function. Make any modifications there? Try sending $SQLQuery out to a file and check that it's valid and what you expect.
mitzyturbo
mitzyturbo
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 Visits: 579
@Nicholas - Running the job on SQL Server 2008 R2, the restores are running on SQL Server 2005

@Bruce - Have commented out the ChangeDBOwner method call but still get the error messages, when its included this throws extra errors saying that the DB cannot be modified as it is in the middle of the restore - if I change the method call to run from within the restore method I get the same error but the DBOwner does change

@Allen - Have ran profiler to extract the sql calls, they all run fine, no error messages or warnings thrown.
mitzyturbo
mitzyturbo
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 Visits: 579
Just wondering if version differences in Red Gate Backup could be the issue i.e. different arguments required per version
i.e. The script specifies SQL Backup v6.5.1.9
However, when I run the scripts on their own through SSMS this is the version that appears in the output. (without specifying SQL Backup version)
bruce 1565
bruce 1565
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 1031
Try modifying the RedgateRestore function, adding the $SQLCommand.CommandTimeout line below.

$SQLCommand = New-Object system.Data.SqlClient.SqlCommand($SQLQuery, $SQLConnection)

$SQLCommand.CommandTimeout = 10000

$SQLCommand.ExecuteNonQuery() | Out-Null


Setting the CommandTimout value to 10,000 was completely arbitrary on my part but for a database that takes 3 hours to restore it works for me.
AllenMWhite
AllenMWhite
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 154
Bruce's suggestion is good, but even better is to set the timeout to 0, so it doesn't matter how long it runs, the script will wait for it.

Allen



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search