Backup Database and Restore Database using C# and MSSQL Server

How to Backup Database and Restore Database using C# and MSSQL Server

1. Backup Database

 Using Stored Procedure to select tables in database to DataSet

public DataSet BackupDatabase()
        {
            var ds = new DataSet();

            var tables = SelectAllTableName();

            var command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;

            foreach (var table in tables)
            {
                command.CommandText = string.Format("sp_{0}_Get_List", table.TABLE_NAME);
                var dtProducts = db.getDataTable(command);
                dtProducts.TableName = table.TABLE_NAME;

                ds.Tables.Add(dtProducts.Copy());
            }

            return ds;
        }

Method SelectAllTableName

private List<ShopTable> SelectAllTableName()
{
var command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
return db.GetList<ShopTable>(command);
}

Class ShopTable

public class ShopTable
{
public string TABLE_NAME { get; set; }
}

Store Procedure sample

USE [your_database]
GO
/****** Object: StoredProcedure [dbo].[sp_Menu_Get_List] Script Date: 8/15/2016 9:51:14 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
/*
----------------------------------------------------------------------------------------------------

-- Created By: ()
-- Purpose: Gets all records from the Menu table
----------------------------------------------------------------------------------------------------
*/


ALTER PROCEDURE [dbo].[sp_Menu_Get_List]

AS



SELECT
[Id],
[Title],
[Description],
[OrderNumber],
[LinkMenu],
[Status]
FROM
[dbo].[Menu]

SELECT @@ROWCOUNT


2. Restore Database from DataSet

// Restore Database
        public bool RestoreDatabase(DataSet ds)
        {
            try
            {
                foreach (DataTable dt in ds.Tables)
                {
                    SqlConnection conn = new SqlConnection(ConnectionString);
                    conn.Open();
                    SqlCommand comm = new SqlCommand("truncate table " + dt.TableName, conn);

                    Logger.Info(string.Format("Truncate table {0}", dt.TableName));

                    comm.ExecuteNonQuery();
                    conn.Close();

                    SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.KeepIdentity);
                    bulkCopy.DestinationTableName = dt.TableName;
                    bulkCopy.WriteToServer(dt);

                    Logger.Info(string.Format("Write data to table {0}", dt.TableName));


                }
                return true;
            }
            catch (Exception e)
            {
                Logger.Error(e);
                return false;
            }

        }


Application code

Backup to XML file

protected void btnBackup_Click(object sender, EventArgs e)
{
var ds = Provider.DataAccessService.BackupDatabase();

var xml = ds.GetXml();

var physicUrl = Server.MapPath(string.Format("/backup/your_database_{0}_{1}_{2}_{3}.xml", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Ticks));

var isSaveFileSuccess = SaveFile(xml, physicUrl);


if (isSaveFileSuccess)
{
FileInfo file = new FileInfo(physicUrl);
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(file.FullName);
Response.End();
}
else
{

}

}

private bool SaveFile(string xml, string path)
{
try
{
var sw = new StreamWriter(path);

sw.Write(xml);

sw.Close();

return true;
}
catch (Exception e)
{
Logger.Error(e);
Response.Write(e.Message);

return false;
}

}

protected void btnRestore_Click(object sender, EventArgs e)
{

if(fuRestore.HasFile)
{
//StreamReader reader = new StreamReader(fuRestore.FileContent);

//var xml = reader.ReadToEnd();

//ltrContent.Text = xml;

var ds = new DataSet();

ds.ReadXml(fuRestore.FileContent);

Provider.DataAccessService.RestoreDatabase(ds);

ltrContent.Text = MessageConstants.RestoreSuccessful;


}

}