Generate SQL Insert statements for your SQL Server 2000 Database

• Download source - 423 Kb

Introduction

Have you ever had to deploy a SQL Server database to a customer? If you have, then you will know that it is certainly not as simple as deploying an application that uses an MS Access database.

With SQL Server databases, you have 3 deployment options that I know of:

1. Distribute your .mdf and .ldf files to your customer and attach the database to their SQL Server instance using the system stored procedure sp_attach_db (or sp_attach_single_file_db). The downside is that the SQL Server must reside on the same machine as the setup application, which is not always the case.
2. Use Enterprise Manager to generate your database scripts and BCP to export all of your data to files and import them back in during your setup process.
3. Script out your entire database including SQL Insert statements for importing data into the database.

With options 2 and 3, you can deploy a SQL Server database locally or remotely. This article focuses on option number 3.

Background

There are a number of commercial programs that will script out an entire SQL Server database for you, but due to current financial reasons, I couldn't afford to buy one and so I had to write my own program which only took a few days.

The source code for this article uses the SQLDMO library extensively to interrogate a SQL Server database. SQLDMO is a COM library and is installed with SQL Server 7/2000. (Note: the code for this article will only work for SQL Server 2000 databases).

Using the code

(See figure 1 above)

1. When you run the project, enter the connection information for the database you would like to generate scripts for.
2. Enter an existing directory for the output. A subdirectory for the database will be placed in the output directory. Underneath the database folder, there is another folder which is used to store all of the BLOB values as .txt files for Text columns, or .dat files for Image columns.
3. If you do not want to generate SQL Insert scripts for the data in the selected database, then clear this option.
4. Press the [Generate] button to start the process. A progress screen will be displayed. Press the [Cancel] button at any time to cancel the process.
5. After the generation process has finished, browse to the database folder in the output directory to see a list of files:

   osql.exe	SQL utility to send batch statements to a SQL Server instance
   textcopy.exe	Unsupported SQL utility used to copy 'text' and 'image' values into a SQL Server database
   createdatabase.sql	Script to create the database
   buildobjects.sql	Script to build all of the objects in the database
   populatedata.sql	Script containing SQL Insert statements to populate the database with data
   importblobvalues.bat	Batch file to import the blob values into the database. Parameters required are server name, database, username, password, authentication method.
   setup.bat	Batch file to invoke the createobject.sql, buildobjects.sql and populatedata.sql scripts. This batch file is generic to any database. Ability to invoke osql using either Windows Authentication or SQL Server Authentication. Parameters required are server name, database, username, password, authentication method.
   install.bat	Batch file to test the installation. Normally, your Setup Project would invoke the setup.bat and importblobvalues.bat files directly passing the appropriate values gathered from a custom user interface.

   Before you test the installation:

   Please follow steps 6 and 7 carefully to avoid dropping the original database!!

6. Open createdatabase.sql in Notepad and change all occurrences (3) of the original database name to something different.
7. Open install.bat and change the database name to that chosen in step 6 above.
8. To install the database, double-click on install.bat.

Points of Interest

Scripting out Dependent objects first

Even if you use SQL Server Enterprise Manager to script out all of the objects in your database, you will still be left to sort out the order in which objects should be created, as some tables/views/functions and stored procedures may depend on others. This process can be time consuming and tedious.

However, in the article code, the EnumDependencies method was called for each of the objects that may potentially have dependencies and the dependent objects are scripted out first. This should eliminate receiving error messages when the scripts run. Circular references in tables when both tables have foreign keys to the other table would still generate errors though.

Generating a batch file to import BLOB values into a database

When I first started out, I included all BLOB values in the actual INSERT statements. However, when I ran the scripts they produced errors, so I assumed there was a length limit for an INSERT statement.

After a bit of browsing on groups.google.com, I learnt that SQL Server already had sample code to import BLOB values, installed in the C:\Program Files\Microsoft SQL Server\MSSQL\Install directory.

The files of interest are: pubimage.bat, pubtext.bat and instpubs.sql.

The example uses TEXTCOPY.EXE to insert 'text' and 'image' values into the pubs database.

pubtext.bat reprinted below:

@echo off
if "%1" == "" goto usage
echo.
echo Inserting images into pubs database on server %1
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '1389'" -Falgodata.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '0877'" -Fbinnet.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '9901'" -Fgggg.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '1622'" -F5lakes.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '0736'" -Fnewmoon.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '9999'" -Flucerne.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '1756'" -Framona.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '9952'" -Fscootney.gif
echo Image update complete!
echo.
goto done
:usage
echo.
echo Usage: pubimage ServerName [SAPassword]
echo.
:done

It was only after I was successfully able to script out the pubs database that I learnt that TEXTCOPY.EXE cannot import 'ntext' values. I was very disheartened when I realized this and I still know of no good way to import 'ntext' data into a database. BCP needs format files and file length sizes and can only insert data, not update data.

If anyone knows a good way to import 'ntext' data into a SQL Server database, then please let me know so that I can update this article and provide a complete solution for scripting out an entire SQL Server database.

Conclusion

I've been a fan of CodeProject for some time now and this is my very first article. I hope it will help many of you out when it comes to deploying your SQL Server databases. If you have any suggestions for improvements, please let me know.

History

Version 1. Submitted on 14th April 2004.