Generate full database SQL Scripts

If you ever need to make a copy of a SQL database without the data, or hold a copy of a script capable of building an empty copy on new servers, then the Generate Scripts option is a very handy tool.

In my role as a software developer, I use these scripts to create a blank copy of my development databases on the customer’s servers. I also keep a copy of all previous version of the database script, as it allows me to create an older database structure if needed.

The following screenshots are of “Microsoft SQL Server Management Studio 2008 R2” Right click on the database that you want to generate the script for and select “Tasks” then “Generate Scripts”

I recommend that you select “Select specific database objects” and then click “Select All”

You now have several options for how you want the scripts to be saved.

Click “Advanced” to set some scripting options

The important options are: 

Include IF NOT EXISTS
Adds a check to the script what will only create new objects if they don’t currently exist.

Script for SQL Version
Allows the script to be compatible with previous versions of SQL (2000, 2005, 2008 & 2008 R2)

Script USE Database
Add a parameter that makes sure that the database affected by the scripts is the correct one. I keep this off as this allows the script to create a copy of the database with a different name.

Types of data to script
This option will choose if the script is to be just the structure of the database or if it should include the data within.

Script Indexes
Sets the script to generate any indexes that you have setup on the database.