How to export database schema with data in Microsoft SQL Server Management Studio

| By Webner

Problem: When we use Microsoft SQL Server Management Studio to export the database, by default it will export the database schema only. When I was trying to export the database to import it into my other local machine, I faced this issue. I selected to export the database in a SQL file. But when I imported it then it imported the database schema only. When I checked the file, it contained the schema only.

Solution: To resolve this problem, follow these steps:

  1. After Opening Microsoft SQL Server Management Studio, select your database then right-click on it and choose the option Tasks>>Generate Scripts…


    It will open the Generate Script Information Window. Click on the Next button to start:

    Database|script info

  2. Now in the Choose Objects dialog box, you can choose whether you want to script the entire database or specific database objects:

    Database|dialog box
    Click Next

  3. Now, you will see the Set Scripting Options dialog box so here choose the second option Save as script file option and select the path where you want to store your file. Then click on the Advanced tab:

    Database|script option

  4. In the Advanced Scripting Options dialog box when you will scroll down in the General section, you will see Types of data to script field and it will have the default value as Schema only:


    This is a dropdown box and you will need to choose the option Schema and data:

    And this is the hack 🙂

  5. Now, after clicking on the Next button, you will see the selected option on the Summary dialog box so click the Next button:


  6. And on the final dialog box, you can see the progress result for all your data base tables. Finally, click on the Finish button and you are done.


Leave a Reply

Your email address will not be published. Required fields are marked *