How to Script MS SQL Server 2008 Database using SQL Server Script Wizard

If you are working as a SQL Developer or a MS SQL Server Database Administrator (DBA), you are probably required to script your sql databases with each object in the database as well as table data.
Many database administrators require SQL Server script database tools frequently.
Prior MS SQL Server 2008 scripting database objects might be done successfully but not so much easy as it is now with MS SQL 2008 databases.
Scripting data was not a build-in feature in SQL Server 2005 instances. Now with the MS SQL Server 2008 editions, you can easily script data in any sql database table within a few steps in Generate SQL Server Scripts Wizard.

Each new version in Microsoft SQL Server family is introducing a new great feature for programmers and for admins, one of the enhancements in MS SQLServer 2008 is SQL Server script database wizard.

Script Task

Open the Microsoft SQL Server Management Studio.
Connect to the SQL Server instance which the database you want to script belongs to.
In the Object Explorer window, drill to the database you want to script objects as well as table data.
Right click on the database which you want to script all database objects.
On the context menu, select Tasks and then choose Generate Scripts... menu item.
We are now starting a new tool SQL Server script database wizard.

generate sql scripts task on sql server 2008

Generate Scripts task menu item will run the Generate SQL Server Scripts Wizard.
The Generate SQL Server Scripts Wizard enables t-sql developers and SQL Server database administrators generate scripts for databases and for any object or objects inside the sql databases as well as data inside database tables.

ms sql server 2008 script wizard

Select the database you want to script database objects on the Select Database screen.

select sql database for scripting database objects

The next screen of the Script Wizard is Choose Script Options screen.
This screen is the place where you can configure the features of your database scripts.
You can identify data scripting, trigger scripting, script for defaults, collation, permissions, statistics, extended properties, as well as create and drop objects, etc.

sql server script database options

General Options
ANSI Padding
Append to File
Continue scripting on Error
Convert UDDTs to Base Types
Generate Script for Dependent Objects
Include Descriptive Headers
Include If NOT EXISTS
Include system constraint names
Schema qualify object names
Script Bindings
Script Collation
Script Create
Script Defaults
Script Drop
Script Extended Properties
Script for Server Version
Script Logins
Script Objec-Level Permissions
Script Statistics
Script USE DATABASE

Table/View Options
Script Change Tracking
Script Check Constraints
Script Data
Script Data Compression Options
Script Foreign Keys
Script Full-Text Indexes
Script Indexes
Script Primary Keys
Script Triggers
Script Unique Keys

After you have identified the scripting properties of your database script, next screen is identifying the object types you want to create scripts.

choose sql server 2008 object types for database script

You choose among sql database object types "stored procedures", "tables", "user-defined table types" and "user-defined functions".

Next screen is choosing from the list of all stored procedures in the related sql database. Check option Select All for scripting for all stored procedures.

Script all stored procedures

Following screen in the sql script wizard is the Choose Tables screen. Click the Select All button for enabling scripting for all database tables.

sql script database tables

You can further script database objects like user-defined table types.

script database objects user-defined table types

And you can identify database objects among user-defined functions like shown below in the following script wizard screen.

sql script database objects like user-defined functions

After selecting all the SQL Server 2008 database objects that you want to create scripts for, it is time to define the output properties of the scripting wizard.
You can script to a file, create a single script file or separate scripting files per object in the database.
You can enable scripting wizard to script to the clipboard or script into a new Query Window.

sql server script wizard output options

Last screen before we can finish sql database script configuration and start scripting all objects in the sql database is the Script Wizard Summary screen.

Script Wizard Summary screen

When you click "Finish" button, the Script Wizard starts running the scripting process.
A process screen will display the steps or the sub-processes the sql scripting wizard is executing, and their final status.
After the scripting of SQL Server database objects task is completed the resultant t-sql scripts created by the wizard will be shown in a new query window as we have choosen as output option for our sample.

So we are finished with this SQL Server script database tool tutorial. I hope you will enjoy this new tools for SQL Server.

SQL Server database administrator and T-SQL programmers can also refer to below listed SQL Server article and guides about SQL Server tutorial to generate database object scripts:
1) How to Script MS SQL Server 2008 Database using SQL Server Script Wizard
2) Script Data in MS SQL Server 2008 Database Tables using Generate SQL Server Script Wizard
3) How to Script Data in MS SQL Server 2012 using Generate and Publish Scripts Wizard
4) SQL Server BCP Utility with sp_HelpText to Generate Script File for Each Stored Procedure in a Database
5) Create SQL Server Database Object Script using Visual Studio 2015