Slow network connections and long running scripts aren’t a good combination. The result will often be a timeout, that most likely will not be solved with a retry. When you use DbUp a quick fix for that problem is right around the corner.
I use DbUp not only for schema migrations, but also to add seed data to the database. That leads to big files full of INSERT-commands to prepare the database for a load test. Unfortunately, those files can take so long to run that a timeout exception is thrown. How much time you have before the timeout exception is thrown depends on the driver. For SQL Server you most likely use System.Data.SqlClient with its 30 second predefined value.
Set Execution Timeout
Problems like this one are common and can be quickly solved in the fluent configuration of your DbUp runner. You need to add a call to WithExecutionTimeout()
and add a big enough TimeSpan
as a parameter:
1 2 3 4 5 6 7 |
var upgrader = DeployChanges.To .SqlDatabase(connectionString) .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly()) .LogToConsole() .WithExecutionTimeout(TimeSpan.FromSeconds(180)) .Build(); |
With that addition your script can now run for up to 3 minutes before a timeout exception is thrown.
Conclusion
If you run in a timeout exception and can’t split your big SQL files, then DbUp offers a nice interface to set a higher timeout. Features like this one makes DbUp so great.
Export Schema and Data from database ?? Maybe using SMO and C#, or SMO and Powershell ?
https://www.red-gate.com/simple-talk/sql/database-administration/automated-script-generation-with-powershell-and-smo/
Hi,
Thanks for the link. I used the RedGate tools often but with DbUp we solve a different problem. We use schema migrations to keep the database up at the level of our application. It’s an interesting approach that so far works very well with our style of programming.
Regards,
Johnny
Another option is to use BULK INSERT and pull from a file like a BCP native export or a CSV. This seems to work fine for me with DbUp – you can pass in a variable to your SQL script with the path of the data file.