SQL Server: Generate Scripts With / Without Data from Database
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.
3. Click the “Next” button in the Generate and Publish Scripts 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.
6. In the Set Scripting Options window, select “Save scripts to a specific location” as the Output type and click the Advanced button.
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.
8. Review all your selections on the Summary page and click the Next button to proceed.
9. Finally, click the Finish button to close the window.
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.
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.
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.
4. Enter server name, select the database and click the OK button.
5. The tool will scan and load all the objects of the selected database.
6. Then, click the Export button to continue.
7. Now, select SQL Server Compatible SQL Scripts.
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.
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.
10. Finally, click the Save button.
11. Set a location where the application will create script files on your system.
12. Congratulations, the process is complete and you have your script file in your system.
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.
