May 16, 2012 at 9:16 am
Hi folks
I'm trying to adapt the powershell script at [/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
May 16, 2012 at 10:02 am
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.
May 16, 2012 at 10:06 am
That was my first approach alright
But if the SQL is wrong I wouldn't have expected the databases to restore.
May 16, 2012 at 10:08 am
What version of SQL Server are you running?
May 16, 2012 at 10:31 am
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.
May 16, 2012 at 1:11 pm
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.
May 17, 2012 at 4:29 am
@nicholas - Running the job on SQL Server 2008 R2, the restores are running on SQL Server 2005
@bruce-2 - 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.
May 17, 2012 at 6:57 am
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)
May 17, 2012 at 7:18 am
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.
May 17, 2012 at 7:35 am
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
May 17, 2012 at 7:47 am
Timeout option did the trick, thanks a million guys
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply