Taking Script From Database In SQL Server In Different Mode

Introduction

This article explains how to take the script from database in SQL Server in a different mode. Normally, we take backup of our entire database as “.bak, .mdf, .ldf” files but sometimes, we need to alter, update, or delete and then create our database based on the modification.

After the first release of our application, if we want to modify any logic or concept in our database, we need to alter the script without needing an entire database. We can take a different type of script in SQL Server for altering. There are three important types of scripts as mentioned below.

• Schema Only
• Data only
• Schema and data
  
  
  

Steps for taking script

Schema Only

The Schema only is normally what we are using while taking the script in SQL Server. The Schema is an all SQL object that creates a table, stored procedure, and view query.

Step 1

Open SQL Server, go to the database which we need the script from. Right click on your database, click Task, and go to Generate script.



Step 2

"Generate and Publish Scripts" window will open. Here, click Next.



Now, we need to select the database object to take the script. We can select two types of options to take the script. We can take the script of the entire database or we can select specific tables, views, and stored procedures as per our need.

If we select the entire database, the script will generate all tables, views, stored procedures and everything. If we select specific database object, then the script will generate the objects we selected.

Click Next button after you choose your option.



Step 3

After clicking the Next button, “Set Script Options” tab will open. We can save the script file in different ways.

1. Save in Specified path - we can save script file in the specified path in our local system.
2. Save to Clipboard - all script stored in the clipboard so we can paste in anywhere.
3. Save new query window - all script are saved in the new query window.
   
   
   

Step 4

Here, we select "Save to new query window" and click the advanced button.

Go to “Script DROP and CREATE” option in advance script options window and select “Script CREATE”. Script DROP and CREATE there are three important types. There are following here.

1. Script CREATE – If select this one, SQL server object CREATE script will generate.
2. Script DROP and CREATE – DROP SQL server object and regenerate again. We do not use this is in production server because if use drop means all data will be erased so we need to use very carefully.
3. Script Drop – only DROP script generate if select this option while taking the script.

Scroll down the advanced script option, go to “Type of data to script”, select “Schema only”. The schema only is the first type of taking the script.



Step 5

Now, click the ok button and click next button, we can see the summary of SQL server object, again click next button. We can see all status of all SQL server objects looks like below screenshot.



We can see the above all SQL server object script is successfully finished so now click finish button. Now open a new SQL window with our script which looks like below.



Data Only

This is the second way for making the script. Here we get the data only. In Data Only we follow the same steps up to step 4. Go to “Script DROP and CREATE” option in advanced script options window and select “Script CREATE”. Scroll down advanced script option, go to “Type of data to script” select “Data only”.



Now, click the ok button and click next button, we can see the summary of SQL server object, again click next button. We can see all status of all SQL server objects and click finish button. We can only see the data with insert query.



Schema and data

This is a combination of Schema Only and Data Only. Here we get the script with data. In Schema and data, we follow the same steps up to step 4. Go to “Script DROP and CREATE” option in advanced script options window and select “Script CREATE”. Scroll down advanced script option, go to “Type of data to script” select “Schema and data”.



Now, click the ok button and click next button, we can see the summary of SQL server object, again click next button. We can see all statuses of all SQL server objects and click finish button. We can see the script with data insert query.



Conclusion

This article explained about how to take the script in SQL Server in a different way base on our needs. I hope this helps students and freshers as well as those who are just learning.