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…
A new windon is open. Please click on NEXT button to continue…
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)
Please 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.
Next, 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.
Check if all Results are Successful and click Finish.
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.