Create Database scripts
tchaplin@miningis.com.au
Posts: 20
I've been looking at using the toolkit to put some databases in version control. I am making scripts which recreate the database from scratch.
I've been using the SqlCompare API to compare a database to a null database, and thus generate a create script. So far, it creates all database objects perfectly.
One thing I can't get it to do is to recreate the database itself, as it was. In particular, I need to script the following:
I don't mean I want to manually set them, I want the toolkit to detect them.
The database owner part may be hard - you would have to create the database with some default owner, then create the owner user, then make it the owner.
Most of these issues arise because I have CLR procedures that have WITH PERMISSION_SET = EXTERNAL_ACCESS.
I have also been looking at the SQL Packager and it's API and I can't seem to find where it might do the database creating. Am I correct in assuming there is no output 'CREATE DATABASE' script, but it is done in code?
If you have any suggestions for scripting the database and it's options and properties it would be very much appreciated.
I've been using the SqlCompare API to compare a database to a null database, and thus generate a create script. So far, it creates all database objects perfectly.
One thing I can't get it to do is to recreate the database itself, as it was. In particular, I need to script the following:
-
The default collation.
The database owner.
The TRUSTWORTHY option set to true.
I don't mean I want to manually set them, I want the toolkit to detect them.
The database owner part may be hard - you would have to create the database with some default owner, then create the owner user, then make it the owner.
Most of these issues arise because I have CLR procedures that have WITH PERMISSION_SET = EXTERNAL_ACCESS.
I have also been looking at the SQL Packager and it's API and I can't seem to find where it might do the database creating. Am I correct in assuming there is no output 'CREATE DATABASE' script, but it is done in code?
If you have any suggestions for scripting the database and it's options and properties it would be very much appreciated.
Comments
Thanks for posting. SQL Toolkit's schema synchronization API does work entirely in the database being synchronized, so it does not offer any SMO-like database creation functionality out of the box.
Database creation would need to be done outside of SQL Toolkit using ADO .NET technology built into the .NET Framework as part of the System.Data.SqlClient namespace. SQL Packager can create a database with a minimal set of options without having to write any extra code, as you've pointed out. If you wanted to expand on the options that are used to set up the database, then it would be fairly straightforward to modify the SQL Packager Code Template to do it. The template comes in the SQL Packager installation folder in the SQL Packager Code Templates\c# subfolder in the form of a Visual Studio 2003 C# Windows Forms application. This can be modified to add some options to the database before running the packaged migration scripts if you wish.
I hope this at least points you in the right direction.
For your information, I'll try to explain what I've been trying to do:
We have several clients that have several different custom built databases, but they are based roughly around the same code base. We are trying to keep track of who has what in their database schemas, so we can roll out changes to everyone. Being custom, the clients themselves can make changes, so they actually control the 'master copy'(!).
I was hoping to able simply grab the whole database as scripts from a clients production databases and easily rebuild them on our office servers, exactly as it was. I'm beginning to see it's not as automatic as I had originally hoped.
For example, if the client is using a linked server somewhere or has windows domain users in the database, then it can't build. It gets impractical when you start involving things that exist outside the database.
It looks like there will have to be some manual intervention every time we rebuild, but that should be ok. We can maybe even work around some of these issues by using the toolkit.
The toolkit and Sql Compare GUI are fantastic though. Our database developers will really appreciate being able to see all the differences between databases, and even generate the change scripts!
That is a limitation of Packager: It will not create linked servers for you. The linked servers would need to be set up before running the package using sp_addlinkedserver.