Home » Blog » SQL Server » SQL Server: Generate Scripts With / Without Data from Database

SQL Server: Generate Scripts With / Without Data from Database

author
Published By Nilesh Kumar
Debasish Pramanik
Approved By Debasish Pramanik
Published On June 6th, 2024
Reading Time 5 Minutes Reading
Category SQL Server

Generating a table script means the creation of a particular table of a particular database. On the other hand, generating a database script means the creation of all the tables, stored procedures, views, and other objects of a particular database. Now, the question is what’s the use of generating script with or without data.

The answer is backup and restore. It’s very easy to generate a script file and later run it on another SQL server/ machine to create the entire database with or without data.

Another reason is that most DB admin does not wish to alter server machines frequently. So, they choose to generate a script file and later run it on the machine.

In addition, with the new GDPR guidelines, organizations need to maintain old data in the database which makes it heavy. In such a case, it’s better to create a script file with the same database schema and data and generate the table to keep in another system or DB.

Generate Scripts from Database in SQL Server

1. Open SQL Server Management Studio (SSMS) and connect your database.

2. Right-click on the database, then click on “Tasks” and choose the “Generate Scripts…” option.

generate script file

3. Click the “Next” button in the Generate and Publish Scripts Introduction window.

introduction window

4. In the Choose Objects window, choose Script entire database and all database objects to create SQL server script file for database.

5. Now, click the Next button.

choose objects window

6. In the Set Scripting Options window, select “Save scripts to a specific location” as the Output type and click the Advanced button.

choose script options window

7. Now, this is the part where you select the 3 script types:

  • Schema only – Generate scripts without data i.e., schema only
  • Schema and data – Create scripts with data and schema
  • Data only – Create scripts with data only

Click the Next button to continue.

select schema and data

8. Review all your selections on the Summary page and click the Next button to proceed.

summary page

9. Finally, click the Finish button to close the window.

finish button

10. It will create the scripts file(s) in the Target location.

The steps may vary according to the SQL server version.

Generate Scripts from Table Only in SQL Server

We already covered this part in the previous section. So, here we are just highlighting the section where you get to choose whether you wish to generate scripts from particular tables only.

Execute the previously defined steps till the 4th step i.e., the Choose objects window. Here, you have two options:

  • Script entire database and all database objects – To create SQL server script file for database.
  • Select specific database objects – To create SQL server script file for particular database objects like tables.

Choose the second option and select the tables in particular that you wish to generate scripts from. The rest of the steps remains the same.

The steps are similar for creating script files from other objects in SQL server as well.

A Professional Utility for SQL Script Creation

With SSMS methodology, you can generate scripts file from the SQL server database for your use. But, is it the only way? No, it isn’t.

Introducing the SQL Server data migration tool that allows you to easily generate scripts with data or without data from SQL Server.

Download Now Purchase Now

You can even generate SQL script from .mdf file and .ndf file with this application. The feature that separates it from the SSMS method is that the steps remain the same for all the SQL Server versions. You do not need to be highly technical to execute this method. And the fact that it can even work with a corrupt database is a big plus.

Steps to Generate Database Scripts for Schema and Data:

1. Download the migrator on your system, then install and run it.

2. Click the Open button.

open sql migrator

3. You have two options:

  • Online Mode – To automatically load all the databases from the authenticated SQL server
  • Offline Mode – To browse and select particular MDF, NDF files from the system

Here, we are proceeding with the online mode.

online mode

4. Enter server name, select the database and click the OK button.

click ok

5. The tool will scan and load all the objects of the selected database.

6. Then, click the Export button to continue.

click export

7. Now, select SQL Server Compatible SQL Scripts.

select sql scripts file

8. Select the objects that you wish to create a script file for. This is the place where you choose whether you wish to generate scripts for a complete database or particular tables or other objects.

select database objects

9. Next, you have two export options:

  • With only schema – This option lets you export only database schema for selected objects in a script file.
  • With schema and data – This option lets you export the entire schema with data for selected objects in a script file.

export sql schema

10. Finally, click the Save button.

 

11. Set a location where the application will create script files on your system.

browse target folder

12. Congratulations, the process is complete and you have your script file in your system.

export complete

The software has many other functionalities that help a user in merging two databases of SQL server, as well as recovery of deleted objects in SQL Server and many more.

Verdict

Generating a Script file in SQL server is beneficial in many situations. But, this very beneficial procedure becomes a long and tiring process if done manually for each database. Hence, we recommend the professional migrator tool to create SQL server script files for database objects like tables, views, etc. without any hassle. It can even process the offline MDF files.

  author

By Nilesh Kumar

As a Chief Technical Writer, I know the technical issues faced by home and professional users. So, I decided to share all my knowledge via this blog. I love to help you with challenges while dealing with technical jargon.