In MS SQL there is a tool for generating base scripts (right click on the base, then Tasks -> Generate Scripts -> all tables + in the advanced settings, replace Types of data to script with Schema only with Data only).

I use this tool to make a backup of the database from the production server: this is appharbor, I can not backup locally and pick up a file from the server.

Is it possible to launch this tool automatically, programmatically, for example from c #? I want to go to regular backups on a schedule, and not to make a copy of the "just in case" occasionally.

On the server is SQL Server 2008 R2.

  • All the labels in the database can be obtained as follows - tym32167

2 answers 2

Generating a script is not the most convenient option for exporting the database along with the data.

There is a utility sqlpackage, which allows you to export / import the database in the format bacpac. This is the standard way to import / export databases for Azure SQL , where there is no direct access to the server:

sqlpackage.exe /Action:Export /ssn:tcp:<ServerName>.database.windows.net,1433 /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<TargetFile> /p:Storage=File

The corresponding items in the context menu in SMSS - Import / Export Data Tier Application.

Use bulk insert for import, which is much faster than simple line-in insertion.

  • Excellent tulzovina, did not know about it. Thank! - tym32167
  • I also did not know, but it turns out to be regular. Galka on this answer, as it closes the meta-need, not the stated. Checked - works with apphabror. On the second answer, add competitive points. - AK ♦

To do this, use SQL Server Management Objects.
A sample code in C # for generating scripts can be viewed by reference.
I use the Powershell script myself - link to the finished script
In your case, you will need to add a parameter in the examples to include the data in the export ScriptingOptions.ScriptData = True more about the parameter