I can manually generate a schema with data via
Table Name > Tasks > Generate Scripts…
But it is possible to create a schema+data single sql file backup of a database and run it on another database programmatically ?
Maybe some SQL Script which will be scheduled by Job or by another approach, i dont know.
Thank you
Best Answer
It is possible to generate DDL and Data from SQL Server Management Studio (SSMS):
Object Explorer > yourServer > yourDatabase > right-click > Tasks > Generate Scripts ...
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?