sql server – Generate Schema and Data to SQL script and Run it on another DB programmatically

It is possible to generate DDL and Data from SQL Server Management Studio (SSMS):

Object Explorer > yourServer > yourDatabase > right-click > Tasks > Generate Scripts …

enter image description here

Within the ‘Advanced’ options change ‘Types of data to script’ to ‘Schema and Data.

Also, here is a simple example of using Powershell to script data for tables I did a few years ago:

Example: Scripting Data with SMO and Powershell

So it’s possible, and you will find many Powershell examples for scripting tables. However backup and restore would be much more straightforward! If the tables are empty, then it’s inconsequential if you move them over right?