Script database sql server

Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInShare on RedditShare on Tumblr

A couple of days ago I had a request to script a list of objects in order to move them to another database instance (get a list of objects from Database A [SOURCE] and move them to Database B [TARGET]). I had to script for both schema and data. Lucky for me SQL Server 2012 has a feature (Generate and Publish Scripts wizard) that helps users to create a script file containing all the necessary information.

How to Script database sql server in 4 easy steps

Step 1: Open Generate and Publish Scripts wizard
Open SQL Server Management Server. Right click on your database[SOURCE] and select Tasks > Generate Scripts…

Script database sql serverA new windon is open. Please click on NEXT button to continue…

Script database sql server 2

Step 2: Choose Objects
On this step you have 2 options. You can choose between:

  • Script the entire database and all database objects
  • Select specific database objects (tables, views, stored procedures, user-defined functions, user-defined data types, DDL triggers, XML schema collections, schemas)

Script database sql server 3Please click on NEXT button to continue…

Step 3: Set Scripting Options
This is the most important step so please be careful what options you select!!!
Choose the output type:

  • Save scripts to a specific location
  • Publish to Web service

On this example I selected the first option.

Script database sql server 4Next, go to Advance to select the type of the data to script:

  • Data only
  • Schema and Data
  • Schema only

In this example I selected the second option Schema and Data.

Script database sql server 5
Script database sql server 6Check if all Results are Successful and click Finish.

Script database sql server 7

Step 4: Run the script file
At the end the generated script can be executed using SQL Server Management Studio(A) or SQLCMD(B). I recommend the last option when the script file is too big.

A) Open the sql file with SQL Server Management Studio, select the database[TARGET] where you want to create the new objects and Execute the script.

B) In the SQLCMD Utility type the below line and hit enter.
SQLCMD -S LOCALHOST -d Database -i C:\script.sql -E
LOCALHOST – is the name of the server.
Database – is the name of the database instance.
C:\script.sql – is the path where the sql file is located.
For more information about the SQLCMD Utility please follow this link.