1

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.

2
  • 2
    There is no "data only" backup and what you are doing is not a backup. Why don't you take a normal full database backup?
    – sepupic
    Jun 2 '17 at 14:57
  • 1
    Are you wanting to migrate your data from SQL Server to another DBMS and only want to dump the data? In the "data only backup" are you backing the data up to flat files on the file system or what?
    – JayJay
    Jun 2 '17 at 18:23
1

mssql-scripter

From https://blogs.technet.microsoft.com/dataplatforminsider/2017/05/17/try-new-sql-server-command-line-tools-to-generate-t-sql-scripts-and-monitor-dynamic-management-views/:

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.