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 Microsofts 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.
Well 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:
With some background about the uses of Scripting, lets look at how its done.
Microsoft SQL Server as the subject of this blog. Data Access Worldwide recommend installing and using Microsofts SQL Server Management Studio (SSMS) with SQL Server, at minimum on your development PC. Its an excellent, free visual tool that works with all versions/editions of Microsoft SQL Server, including Express.
Using SQL Server Management Studio, you have a series of Scripting options that can be selected during the following steps:
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).
You might notice that the script has the full path to the database files in it:
CREATE DATABASE [Chinook]
CONTAINMENT = NONE
( NAME = NChinook, FILENAME = NC:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS16\MSSQL\DATA\Chinook.mdf , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
( NAME = NChinook_log, FILENAME = NC:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS16\MSSQL\DATA\Chinook_log.ldf , SIZE = 73728KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
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]
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.
You can modify some of the steps as needed. Some examples:
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.
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 developers tool chest.