Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
in SQL Server by 14 17 25

I generated scripts for a database in SQL Server 2016, I ran these scripts files on SQL Server 2012 to create a database with data. but I had noticed that only tables structures are created with no data. 

Missing data in SQL Generate Scripts

I'm facing this problem with all tables!

1 Answer

1 like 0 dislike
by 155 174 353
selected by
Best answer

SQL Generate Scripts with Data

In SQL Server, you can generate scripts with Schema and Data, but you should be aware of the default value for the Type of data of script is Schema Only as shown below:

Missing data in SQL Generate Scripts

Therefore, during generating scripts, If you didn't set the Type of data of script is Schema and Data in the Advanced scripting options, the generated scripts will not include the data, and it will only include the schema. I think it's your case!

Note: The Generate scripts process is not suitable for large databases, check other proposed solutions and workaround at Database was backed up on a server running version 13

How to Generate Scripts with Data and Schema in SQL Server?

To generate scripts in SQL Server, you should do the following:

  1. Right-click on your database > Select Tasks > then click on Generate scripts.

    Generate Scripts in SQL Server

  2. The Generate and Publish Scripts dialog Should be opened > Click Next.

    Generate and Publish Scripts for database objects in SQL Server

  3. Select the entire database objects or select specific views and tables as you prefer.

    Generate Scripts to the entire database objects

  4. At set script options, click on advanced to set the Advanced scripting options.

  5. Scroll down to Type of data of script setting and set it to Schema and Data.

    type of data of script

  6. Review the summary.

    Generate scripts summary

  7. Wait for a moment until the script is generated successfully then click finish.

    Generate scripts Success

Generate Scripts Considerations

Before applying the generated script on the destination SQL Server instance, you should firsts perform the below checklist:

  1. Make sure that there is no database with the same name on the destination SQL Server instance.
  2. Change the location path for the date (MDF) and log (LDF) files with the corresponding data and log location at the destination SQL Server instance.

    change data location in SQL server-min

  3. You may face many issues related to the new functionality that not exists on the old version, so try to comment the lines that raise these issues on your script and delete the created database (if it's already created) then rerun the script again.
  4. for a large database, if you don't have any other option, try to generate scripts for each object one by one.
  5. Before running the generated scripts, make sure that you have sufficient disk space on your destination server.
  6. After running the generated script, try to perform a quick smoke test by selecting the count for each table at the source database and compare it with the corresponding table on the newly created database.
0 0
is this script procedure on the server? or on the new pc?
If you don’t ask, the answer is always NO!