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:
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:
Right-click on your database > Select Tasks > then click on Generate scripts.
The Generate and Publish Scripts dialog Should be opened > Click Next.
Select the entire database objects or select specific views and tables as you prefer.
At set script options, click on advanced to set the Advanced scripting options.
Scroll down to Type of data of script setting and set it to Schema and Data.
Review the summary.
Wait for a moment until the script is generated successfully then click finish.
Generate Scripts Considerations
Before applying the generated script on the destination SQL Server instance, you should firsts perform the below checklist:
- Make sure that there is no database with the same name on the destination SQL Server instance.
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.
- 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.
- for a large database, if you don't have any other option, try to generate scripts for each object one by one.
- Before running the generated scripts, make sure that you have sufficient disk space on your destination server.
- 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.
