Recently I had to deploy a project which used a Microsoft SQL Server database. During the deployment process all the tables and some predefined data should be created in the database of the machine on which the application is installed. I needed a simple .sql file which contains SQL instructions to create the schema and to insert the data. I thought that this would not be a big deal because SQL Server Management Studio has many tools and functionalities and for sure also one to do this since it is an essential part of software deployment. I used the export Wizard and everything worked well except that with the Wizard it is only possible to export the schema. I was very surprised that it is not possible to export the schema and the data.
After some research on the internet I found several commercial tools which are able to export also the data but I did not want to purchase such a tool only to export the data.
I found that there is an "API" for the SQL Server which is called SQL Server Management Objects (SMO) and SQL Server Replication Objects (RMO). These two "API's" can be used to get information from an SQL Server such as all the databases, all the tables in a database and all other information related to an SQL Server. I played a bit with them and there exists a method for each server object (such as databases, tables,...) which is called Script(). This method returns a StringCollection which can be used to re-create the SQL object. It is worth to give a look if you have to create, update or delete SQL objects from within code.
I tried to build a tool which is able to export a whole database, all its objects (function, triggers,...) and the data to a single sql file. I faced several problems. First it is important in which order the tables and constraints are created because otherwise the script would not run successfully. Second I had to generate insert statements for the data which has to consider all datatypes supported by SQL Server.
In some way I would have been able to solve these two issues but then I found the Microsoft SQL Server Database Publishing Wizard 1.1. This tool by Microsoft does exactly what I need. It exports one or more databases with their schema, objects and the data. If you have a similar issue I suggest you to use this one since it is free and worked fine for me :-)
Here you can find the official home page of the publishing wizard tool on CodePlex.
I tried to build a tool which is able to export a whole database, all its objects (function, triggers,...) and the data to a single sql file. I faced several problems. First it is important in which order the tables and constraints are created because otherwise the script would not run successfully. Second I had to generate insert statements for the data which has to consider all datatypes supported by SQL Server.
In some way I would have been able to solve these two issues but then I found the Microsoft SQL Server Database Publishing Wizard 1.1. This tool by Microsoft does exactly what I need. It exports one or more databases with their schema, objects and the data. If you have a similar issue I suggest you to use this one since it is free and worked fine for me :-)
Here you can find the official home page of the publishing wizard tool on CodePlex.
2 comments:
< advertisement >
Hi Manfred,
If you want to create scripts from database changes, then you should take a look at SSW Sql Deploy.
http://www.ssw.com.au/SSW/sqldeploy/
Easy to keep in synch database versions on thousands of clients...
< /advertisement >
:-)
Hello Peter,
thanks for the tipp :-). I had only a quick look at it but it seems to be very interesting. I may try out the trial version as soon as I have some time...
Post a Comment