Question :
I must create a post deploy script based on data from a previous sql server database. I’m making a data-only backup, but it must be done table by table due to avoid the cyclic dependency from the forward keys.
For each table, I go to SQL Management Studio, go to task, generate script, configure the backup and generates data only backup. But this proccess done table by table takes much time and due to have to reconfigure each one of those times and is a quite boring process, it is also very sensitive to human error.
I would like to know if there’s a way to create a script to make it. Can be some command in tsql, sqlcmd or powershell. Google just gives me the full backup for line command or some another process involving some kind of “windowed wizard” in case of backuping just one table, and it does not satisfy my restrictions.
Answer :
mssql-scripter
You can use mssql-scripter on Linux, macOS, and Windows to generate
data definition language (DDL) and data manipulation language (DML)
T-SQL scripts for database objects in SQL Server running anywhere,
Azure SQL Database, and Azure SQL Data Warehouse. You can save the
generated T-SQL script to a .sql file or pipe it to standard *nix
utilities (for example, sed, awk, grep) for further transformations.
You can edit the generated script or check it into source control and
subsequently execute the script in your existing SQL database
deployment processes and DevOps pipelines with standard multiplatform
SQL command line tools such as sqlcmd.
Install instructions on GitHub
Sample usage:
Generate DDL scripts for all database objects and DML scripts (INSERT statements) for all tables in the Adventureworks database and save the script to a file
$ mssql-scripter -S localhost -d AdventureWorks -U sa ––schema-and-data > ./adventureworks.sql