As a part of our nightly and continuous builds, we want to be restore a baseline database prior to running the database build. That way, we are always build from a baseline, which represents the data schema that is present in our production environment.
I currently have a set of scheduled jobs to do this for our nightly environment, prior to the nightly scheduled build.
However, that's not something I can do for the continuous integration environment, that we use to confirm successful check-in of code, and give us warning of potential build issues in our nightly environment.
I am trying to use the SQLExecute activity to run a restore database script. The database restore is initiated, but I keep running into a timeout issue.
The innermost exception is "The wait operation timed out"
Based on the log, I can see that the connection is made and the command is executing, as it has logged between 10 and 15 percent restore completion.
I tried to change the CommandTimeout property for the activity, but this didn't seem to have an impact. I tried to adjust the connection timeout in the connectionString (though, since the connection was happening, I don't believe that was an issue).
Is there a way to execute a script and extend the timeout for the execution of that script? I don't know if this is a bug (if so, I'll log it in the issue tracker) or I'm just not doing something correctly.