Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Powershell Restore Script Expand / Collapse
Author
Message
Posted Wednesday, May 16, 2012 9:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:44 PM
Points: 57, Visits: 299
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: (:) [], 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: (:) [], 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



  Post Attachments 
restoresp.txt (8 views, 7.29 KB)
Post #1301127
Posted Wednesday, May 16, 2012 10:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:36 PM
Points: 199, Visits: 141
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.


Post #1301181
Posted Wednesday, May 16, 2012 10:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:44 PM
Points: 57, Visits: 299
That was my first approach alright
But if the SQL is wrong I wouldn't have expected the databases to restore.
Post #1301188
Posted Wednesday, May 16, 2012 10:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:29 PM
Points: 2,007, Visits: 6,077
What version of SQL Server are you running?



Shamless self promotion - read my blog http://sirsql.net
Post #1301189
Posted Wednesday, May 16, 2012 10:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:36 PM
Points: 199, Visits: 141
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.


Post #1301207
Posted Wednesday, May 16, 2012 1:11 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 22, 2014 6:08 AM
Points: 60, Visits: 890
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.
Post #1301311
Posted Thursday, May 17, 2012 4:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:44 PM
Points: 57, Visits: 299
@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.

Post #1301623
Posted Thursday, May 17, 2012 6:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:44 PM
Points: 57, Visits: 299
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)
Post #1301718
Posted Thursday, May 17, 2012 7:18 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 22, 2014 6:08 AM
Points: 60, Visits: 890
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.
Post #1301744
Posted Thursday, May 17, 2012 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:36 PM
Points: 199, Visits: 141
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



Post #1301755
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse