Red Gate forums :: View topic - Create Database scripts
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Toolkit Previous Versions
SQL Toolkit Previous Versions forum

Create Database scripts

Search in SQL Toolkit Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
tchaplin@miningis.com.au



Joined: 23 Apr 2008
Posts: 15

PostPosted: Wed Apr 23, 2008 2:11 am    Post subject: Create Database scripts Reply with quote

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:


    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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Thu Apr 24, 2008 2:54 pm    Post subject: Reply with quote

Hi Tristan,

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.
Back to top
View user's profile Send private message
tchaplin@miningis.com.au



Joined: 23 Apr 2008
Posts: 15

PostPosted: Fri Apr 25, 2008 3:44 am    Post subject: Reply with quote

Thanks for the suggestion. I will look into it.

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!
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Fri Apr 25, 2008 9:42 am    Post subject: Reply with quote

Hi Tristan,

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group