How to back my database data in a .sql file from C# RRS feed

  • General discussion

  • Hi, 

      I´m writting a C# application which should be able to dump the data of a sql database in a .sql file, in terms of  INSERTs statements. How can i do this from C#?


    thansk in advance,


    Friday, May 27, 2011 8:33 PM

All replies

  • Hi there.

    Take a look at this article, it will describe on how to do that using SQL SMO objects.:

     Script SQL Server database, including lookup tables content, by using Microsoft.SqlServer.Management.Smo

    Hope this helps.



    My blog: InsomniacGeek.com
    Monday, May 30, 2011 3:42 PM
  • Thanks, just in case somebody needs to do the same...this is the method that I wrote...



        private static void BackupDatabase()


            //Create the output backup file

            String todayDate = DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year;

            String backupFileName = backupDirectory + todayDate + ".sql";


            if (File.Exists(backupFileName))





            StreamWriter sw = File.CreateText(backupFileName);


            //Connect to the local, default instance of SQL Server.

            Server srv = new Server(sqlInstanceName);

            Database db = srv.Databases[localDatabaseName];


            //Define a Scripter object and set the required scripting options.

            Scripter scrp = new Scripter(srv);

            scrp.Options.ScriptData = true;

            scrp.Options.ScriptSchema = false;

            scrp.Options.ScriptDataCompression = false;

            scrp.Options.Statistics = false;


            sw.WriteLine("USE [" + localDatabaseName + "]");


            //Disable Constraints

            sw.WriteLine("EXEC sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"");


            //Iterate through the tables in database and script each one.

            foreach (Table tb in db.Tables) 


              if (!tb.IsSystemObject) 


                foreach (string s in scrp.EnumScript(new Urn[] { tb.Urn }))








            //Enable Constraints

            sw.WriteLine("EXEC sp_msforeachtable \"ALTER TABLE ? CHECK CONSTRAINT all\"");








    Wednesday, June 1, 2011 8:10 PM