This worked example demonstrates how to package a database as an .EXE (.NET executable) and run the executable to create a copy of the database.

In the example, the Magic Widget Company has a SQL Server database running on a live Web server. They have created a database of their products, which now needs to be packaged for deployment to the sales department.

You will see how to:

1. Set up the database if you want to follow the example on your own system.
   You will need access to a SQL Server to do this.
2. Specify the contents of the package.
3. Preview the SQL scripts.
4. Generate the package as an .EXE (.NET executable).
5. Run the package to create a copy of the database.

Setting up the database

The worked example packages the WidgetSales database. To create this database on your SQL Server:

1. If it exists already, delete the database WidgetSales from your SQL Server.
2. Click here to view the SQL creation script for the database.
3. Copy the script, paste it in your SQL application, and then run it.
   The database is created and populated with data.

Specifying the package contents

1. If you have not yet started SQL Packager, select it from your Start menu; if SQL Packager is already running, click  New Project.
2. On the Choose a project type page of the Packager Wizard, select Package a database, and click Next.
   The Choose database to package page of the Packager Wizard is displayed.
3. In the Server box, under Database to package, type or select the name of the SQL Server on which you created the database.
4. Select the authentication method, and for SQL Server authentication enter the User name and Password.
5. In the Database box, type or select WidgetSales.
   If WidgetSales is not displayed in the Database list, right-click in the Database box and click Refresh, or scroll to the top of the list and click Refresh.
   .
6. Click Next.
   SQL Packager displays a message dialog box while it analyzes the database structure.
   If you select the Close message box on completion check box, SQL Packager closes this message dialog box automatically the next time that you choose the database. For this example, leave the setting as it is.
7. Click OK to close the message box.
   SQL Packager displays a list of the objects in the database.
   
   You can choose which objects to package. For more information, see Specifying the package contents.
   For this example, leave all the check boxes selected so that all of the objects are created in the database when the package is run.
8. Click Next.
   SQL Packager displays a list of the tables that contain data that you can package.
   
   You can choose the tables for which you want to package data. For this example, leave all the check boxes selected so that you package all of the data.
9. Click Next.
   SQL Packager displays a message dialog box while it generates the SQL script.
   If you select the Close message box on completion check box, SQL Packager closes this message dialog box automatically the next time that it generates the SQL script. For this example, leave the setting as it is.
10. Click OK to close the message box.

Previewing the SQL Scripts

The SQL scripts page is displayed.

To see the SQL script for creating the data, click the Data Script tab. To see details about unexpected behavior that may occur when you run the package, click the Warnings tab.

If required, you can save the scripts on the next page of the wizard.

When you have finished reviewing the SQL scripts, click Next.

Generating the package

The Specify package type page is displayed.

In this example, we will create an .EXE. For an example of how to create a C# project, see Packaging an upgrade as a C# project.

1. Ensure that Package as an .EXE is selected, and click Next.
   The Create .EXE page is displayed.
   
2. Enter a name and location for your package.
3. Leave the Use compression check box selected so that your package will be compressed.
   The generated files will be compressed to approximately 75% of their original size.
4. Select the Run executable now check box, and click Finish.
   A message dialog box informs you that the executable is created in the location you specified. Click OK to close it. For large databases, additional dynamic-link library (.dll) files are also created. The Run Package dialog box is displayed for you to run the executable immediately.

Running the package

You use the Run Package dialog box to specify details of the database that will be created when the package is run.

1. Select the Server on which you want to create the database, and if required, enter the authentication details. 

   The SQL Server version must be compatible with the latest version database that you specified when you chose the database to package.

2. The Advanced options enable you to define properties for the database that will be created, such as the database location.
3. Type a name for the Database and click Run. 
4. A message dialog box is displayed for you to confirm that you want to continue. Click Yes.
5. When the database is created, a message dialog box confirms that the package has run successfully. Click OK to close it.

You can use your SQL application to check that the database has been created as you expect. If you have purchased Red Gate SQL Compare you can compare the databases' structure to confirm that they are identical; if you have purchased SQL Data Compare, you can compare the data to confirm it is identical.