Programatically back up to sql file

Question

In Visual Studio I can export an entire database with all its data, schema, etc..., to a .sql dump file using the "Publish to provider" option. Is there a way to generate that file with C# or VB.NET?

It is generally a bad idea to backup everything to text format. Schema - perhaps, but not data. — Victor Zakharov, Jul 09 '13 at 20:14
Are you looking to back up your DB as a regular .BAK file, or do you want it as DDL and DML statements that would recreate the DB? — Jim, Jul 09 '13 at 20:20
Ok, so you want DDL and DML statements that would recreate your schema and data... — Jim, Jul 09 '13 at 20:23
@Jim, I don't know what DDL and MDL statement means, but yes, I need an sql dump file, such as in mysql. — boruchsiper, Jul 09 '13 at 20:24
DDL - Data Definition Language (CREATE, DROP, etc). DML - Data Manipulation Language (SELECT,INSERT,UPDATE, etc). — Ron.B.I, Jul 09 '13 at 20:32
Why do you want it? This appears to be a [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Are you trying to move the data from SqlServer to MySql or something? There are other (easier) ways to do it than generating a sql dump file. — Scott Chamberlain, Jul 10 '13 at 05:27

Steve · Answer 1 · 2013-07-09T20:31:54.853

2

You can try with this code,

ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = "Cmd.exe";
psi.Arguments = "/C sqlcmd -S servername -E -Q " + 
                "\"BACKUP DATABASE [dbname] TO  DISK = " + 
                "N'D:\\TEMP\\test.bak' WITH NOFORMAT, INIT,  " + 
                "NAME = N'dbname-Complete Backup', " + 
                "SKIP, NOREWIND, NOUNLOAD,  STATS = 10\"";

Process.Start(psi);

Of course you need to replace the servername with the name of your server, dbname with the name of your database and the destination file name (d:\temp\test.bak) with your preferred output name.
At the end you will have a file with a complete backup of your database. You can restore this file only on the same or newer version of Sql Server

edited Jul 09 '13 at 20:31

answered Jul 09 '13 at 20:24

Steve

213,761
22
232
286

Thnx, but I need a .sql file, not .bak – boruchsiper Jul 09 '13 at 20:26
@Steve, from the comments it seems he doesn't want a .BAK file, but instead wants it all dumped to DDL/DML statements, like if you were to use "Script As" in SSMS. – Jim Jul 09 '13 at 20:26
2

@Jim I see, but if I look at this [old article](http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated) this task will be complex and I am not sure if really Worth the effort required. – Steve Jul 09 '13 at 20:36

score 1 · Answer 2 · answered Jul 10 '13 at 05:32

SQL SMO can be an option for you, I used it long back for schema generation, have a look at this , though it initially says only schema it later refers to an option of including scripting of data as well where it says:-

If you want to include the data as well in the script then write
options.ScriptData = true;

score 0 · Answer 3 · edited May 23 '17 at 12:29

0

You can do a number of different backup operations using sqlcmd. For instance, to get a .BAK file you can run the following from a batch file or command line...

sqlcmd -S host\DBName -E -Q "BACKUP DATABASE [Name_of_Database] TO DISK=’X:PathToBackupLocation[Name_of_Database].bak’"

*EDIT

Since OP seems to be looking for DDL and DML to recreate the DB, and not a .BAK file, the above isn't what he needs, but I'll leave it here in case it helps anyone else.

To get to the OP's needs though, you'll probably want to check out the SMO library.

Programatically back up to sql file

3 Answers3