Powershell Restore Script

  • 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

  • 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.

  • That was my first approach alright

    But if the SQL is wrong I wouldn't have expected the databases to restore.

  • What version of SQL Server are you running?



    Shamless self promotion - read my blog http://sirsql.net

  • 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.

  • 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.

  • @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.

  • 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)

  • 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.

  • 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

  • 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