Sharing SQL Server Databases via Scripting

One of the operational challenges that comes with developing and deploying a business applications toolset on the SQL Server database is moving data between different revisions or configurations of Microsoft’s database servers, often while the database is live. “Scripting” is one technique that can be used to address this challenge.

Scripting is a process that generates a plain text script file using the Transact-SQL language (the Microsoft Server version of SQL), which you can view and edit using the SQL Server Management Studio or any text editor and import to another SQL Server instance. The generated text file can include all parts of a database, the schema (database structure) and just the data.

We’ll get to how to use Scripting below. Here is a summary of what Scripting does and some examples of situations where it can be used:

• The Scripting generated text file includes all of the default settings for the SQL Server instance on which the script is run which is what you will often want. That script can be imported to another SQL Server instance with the same or a different configuration. You may, for example, want to copy a database from your development PC running SQL Server Express to a client’s QA or production environment that is running SQL Server Enterprise, with potentially numerous company database policies implemented by a database administrator. Using this simplified script will create the new database on the Enterprise server using all of the policies already in place on that server instance.
• The options “Script for Server Version” and “Script for the database engine edition” make script modifications for compatibility with the specific target SQL Server instance the data will be installed on. These Scripting options are valuable, since they can resolve version incompatibilities that could limit data migration from a newer to an older SQL server instance.
• Other adjustments can be made for source and target server variations. For example, if the current database is on a paid edition of SQL Server and you want to import it to a free Express edition, during script creation, you can change the default target from “Microsoft SQL Server Standard Edition” to “Microsoft SQL Server Express Edition”.

With some background about the uses of Scripting, let’s look at how it’s done.

Microsoft SQL Server as the subject of this blog. Data Access Worldwide recommend installing and using Microsoft’s SQL Server Management Studio (SSMS) with SQL Server, at minimum on your development PC. It’s an excellent, free visual tool that works with all versions/editions of Microsoft SQL Server, including Express.

Generate a Script in the SQL Server Management Studio

Using SQL Server Management Studio, you have a series of Scripting options that can be selected during the following steps:

1. Open SQL Server Management Studio (SSMS)
2. Expand Databases
3. Select the database to script
4. Right-click on the database and select Tasks > Generate Scripts
5. On the Choose Objects page, leave the default selection of ’Script entire database and all database objects’ selected
6. On the ’Specify how scripts should be saved or published’ page, click the Advanced button
7. The last selection in the General properties group is ’Types of data to script’, change this to ’Schema and data’
8. If you know you are sending this to someone with an older SQL Server version, you can change the ’Script for Server Version’ property to the Server version you need (this is why I created 2 versions of the script).
9. Click Ok
10. Change the file location or wherever you want to script to be generated
11. Click Next, the Next again. At this point, the script will be generated.

If you wish to view or edit the resulting script file, I recommend using the Management Studio, since it also gives you contextual coloring and you can test run any script here (be careful not to delete or overwrite any data you need).

Paths

You might notice that the script has the full path to the database files in it:

CREATE DATABASE [Chinook]
 CONTAINMENT = NONE
 ON PRIMARY
 ( NAME = N’Chinook’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS16\MSSQL\DATA\Chinook.mdf’ , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON
 ( NAME = N’Chinook_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS16\MSSQL\DATA\Chinook_log.ldf’ , SIZE = 73728KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 GO

While Transact-SQL supports changing this, the Generate Script wizard does not, so if you want to change the path, since it could be different in the environment where you will run the script, you can change the script.

A simple change is to replace all of the above with this:

CREATE DATABASE [Chinook]
GO

This will generate the database on the SQL Server instance the script is run on using all of the default settings for that server instance, which is probably what you really want.

Options

You can modify some of the steps as needed. Some examples:

• You can modify the Options in step 7 and change the “Script DROP and CREATE” from the default of “Script CREATE” to “Script DROP and CREATE”. This would change the script to drop (remove) the database before creating it, in case it already exists when you run the script.
• Sometimes the script you are generating is intended for multiple version or edition instance use or the version or edition is simply not known ahead of time. If the target instance is undefined (or may change), your best bet is to script for the lowest common denominator, meaning the oldest version and lowest edition (Express for the widest range) you expect the target instance(s) to be running.
• It is also possible that the database you are attempting to script requires a minimum revision or edition because of its structure. If you attempt to script to a version or edition that does not support the database structure the scripting process will generate errors. If that happens you can then select a newer version or more capable edition and generate the script based on that updated minimum combination.
• You can also use this technique to very quickly duplicate a database for testing. Run the Generate Script wizard and save the script to a new query window in the Management Studio, do a find and replace of the database name (e.g. replace [Chinook] with [Chinook2]) and execute the script.

Chinook is a well-known, downloadable sample database.

The things you can do on SQL Server with the tools provided and Transact-SQL are tremendous and can be very complex. This is one simple and efficient way to enable you to exchange (or even back up) SQL Server databases.

Limitations

There are limitations to scripting. For example, it might not work on very large databases.

DataFlex developers Mike Steffano and Sean Bamforth at Austin Programmers Group needed to copy a 5 GB database from SQL Server 2016 to SQL Server 2012. They using the Scripting Wizard in SSMS, but even the 64-bit version of the SQL Server Management Studio could not load the complete script file nor import it into the target SQL Server instance using the SQL Server command line tools. After trying numerous solutions, including third party tools, the solution was to generate scripts for one table at a time.

As with any programming technique or tool, not every solution works for every scenario. There are many techniques and tools for working with SQL data, such as backup and restore, replication and third party tools.

For typical development and testing, scripting is an excellent and flexible tool for your developer’s tool chest.