I'm setting up a process to archive a large database (40Gb+) via Automise. I'm using a 3rd party SQL Server backup product (Red-Gate Software's SQLBackup 5.1) and they offer both a command-line and extended stored procedure. I'd like to utilise the extended stored procedure by preference, and I've had some success with it by employing the "ADO Execute SQL" action; the issue with using that action is there appears to be no way to adjust the ADO timeout setting, and the action fails with large databases because it times out (this issue has been confirmed by Red-Gate). I'd use the "ADO Execute Stored Procedure" action, but the SQL code to execute is passed into the extended stored procedure as an un-named parameter, and that action requires parameters to be named. I'd prefer not to have to resort to running a VBScript if possible; Can anyone advise any other option?
Here is an example of the use of the extended stored procedure:
exec master..sqlbackup N'-SQL "BACKUP DATABASE [LargeDB%] TO DISK = [\\File-Svr\SharedFolder\Filename.sqb] WITH NAME = [LargeDB Backup], DESCRIPTION = [Monthly archive of LargeDB], INIT, VERIFY, COMPRESSION = 1"'
Another thought; would it be possible to add a timeout adjustment thingy to the "ADO Execute SQL" action ala the "ADO Execute Stored Procedure" action?
The ADO timeout is exposed in the “ADO Execute SQL” action, but it’s under the “Output” tab (not the best place for it, but probably because we didn’t want to try to squeeze it into the Query tab). By default it’s set to 30 seconds.
Yes, there is a timeout adjuster on the Output tab. But it's minimum setting appears to be 5 seconds; The SQLBackup people tell me I should be using a setting of 0 seconds, which means wait until the command finishes, otherwise backing up large DB's will fail. And they do! I've tried various settings from 5 to 300 seconds, but they all fail with a timeout error.
Is there any reason the adjuster can't be set to 0? Using the adjuster arrow only goes down to 5 seconds; setting it via the numpad 0 key and clicking OK causes it to be reset to 5 seconds. Why it can't be set to 0?
I don’t see any reason it shouldn’t be allowed to be 0 - I’ll change it tomorrow. In the meantime, you can set it to 0 by using the Action Inspector (tab next to the Project and Action List). But if you open and then OK the action from the dialog, it will set it back to 5. (you could also put in some script to set it to 0 before the action runs…)
