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?
-
1It is generally a bad idea to backup everything to text format. Schema - perhaps, but not data.Victor Zakharov– Victor Zakharov2013-07-09 20:14:08 +00:00Commented Jul 9, 2013 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– Jim2013-07-09 20:20:18 +00:00Commented Jul 9, 2013 at 20:20
-
@Jim, I would like to back up by db to an .sql file.boruchsiper– boruchsiper2013-07-09 20:21:14 +00:00Commented Jul 9, 2013 at 20:21
-
Ok, so you want DDL and DML statements that would recreate your schema and data...Jim– Jim2013-07-09 20:23:15 +00:00Commented Jul 9, 2013 at 20:23
-
1DDL - Data Definition Language (CREATE, DROP, etc). DML - Data Manipulation Language (SELECT,INSERT,UPDATE, etc).Ron.B.I– Ron.B.I2013-07-09 20:32:06 +00:00Commented Jul 9, 2013 at 20:32
|
Show 2 more comments
3 Answers
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
3 Comments
boruchsiper
Thnx, but I need a .sql file, not .bak
Jim
@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.
Steve
@Jim I see, but if I look at this old article this task will be complex and I am not sure if really Worth the effort required.
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;
Comments
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.
See also these SO posts...
https://stackoverflow.com/a/1162348/1246574
Programmatically generate script for all objects in a database
3 Comments
Sam
That won't backup anything.
Jim
@Sam, would you care to elaborate? I copied this directly out of a script that I use in production to create daily DB backups, so I fail to see how it "won't backup anything".
Sam
If you run THAT SCRIPT, it will not create a backup. You stated it would. -S is server name, -E is integrated auth, -Q is the query. "Enter Description Here" is not a query.