I want to write a code to backup my Sql Server 2008 Database using C# in .Net 4 FrameWork. Can anyone help in this.
10 Answers
you can connect to the database using SqlConnection and SqlCommand and execute the following command text for example:
BACKUP DATABASE [MyDatabase] TO DISK = 'C:\....\MyDatabase.bak'
See here for examples.
Comments
It's a good practice to use a config file like this:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="MyConnString" connectionString="Data Source=(local);Initial Catalog=MyDB; Integrated Security=SSPI" ;Timeout=30"/>
</connectionStrings>
<appSettings>
<add key="BackupFolder" value="C:/temp/"/>
</appSettings>
</configuration>
Your C# code will be something like this (note the parameterization of the SQL):
// read connectionstring from config file
var connectionString = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
// read backup folder from config file ("C:/temp/")
var backupFolder = ConfigurationManager.AppSettings["BackupFolder"];
var sqlConStrBuilder = new SqlConnectionStringBuilder(connectionString);
// set backupfilename (you will get something like: "C:/temp/MyDatabase-2013-12-07.bak")
var backupFileName = String.Format("{0}{1}-{2}.bak",
backupFolder, sqlConStrBuilder.InitialCatalog,
DateTime.Now.ToString("yyyy-MM-dd"));
const string query = @"
BACKUP DATABASE @db
TO DISK = @file;
";
using (var connection = new SqlConnection(sqlConStrBuilder.ConnectionString))
using (var command = new SqlCommand(query, connection))
{
command.Parameters.Add("@db", SqlDbType.NVarChar, 128).Value = sqlConStrBuilder.InitialCatalog;
command.Parameters.Add("@file", SqlDbType.NVarChar, 255).Value = backupFileName;
connection.Open();
command.ExecuteNonQuery();
}
6 Comments
Works for me:
public class BackupService
{
private readonly string _connectionString;
private readonly string _backupFolderFullPath;
private readonly string[] _systemDatabaseNames = { "master", "tempdb", "model", "msdb" };
public BackupService(string connectionString, string backupFolderFullPath)
{
_connectionString = connectionString;
_backupFolderFullPath = backupFolderFullPath;
}
public void BackupAllUserDatabases()
{
foreach (string databaseName in GetAllUserDatabases())
{
BackupDatabase(databaseName);
}
}
public void BackupDatabase(string databaseName)
{
string filePath = BuildBackupPathWithFilename(databaseName);
using (var connection = new SqlConnection(_connectionString))
{
var query = String.Format("BACKUP DATABASE [{0}] TO DISK='{1}'", databaseName, filePath);
using (var command = new SqlCommand(query, connection))
{
connection.Open();
command.ExecuteNonQuery();
}
}
}
private IEnumerable<string> GetAllUserDatabases()
{
var databases = new List<String>();
DataTable databasesTable;
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
databasesTable = connection.GetSchema("Databases");
connection.Close();
}
foreach (DataRow row in databasesTable.Rows)
{
string databaseName = row["database_name"].ToString();
if (_systemDatabaseNames.Contains(databaseName))
continue;
databases.Add(databaseName);
}
return databases;
}
private string BuildBackupPathWithFilename(string databaseName)
{
string filename = string.Format("{0}-{1}.bak", databaseName, DateTime.Now.ToString("yyyy-MM-dd"));
return Path.Combine(_backupFolderFullPath, filename);
}
}
2 Comments
The following Link has explained complete details about how to back sql server 2008 database using c#
Sql Database backup can be done using many way. You can either use Sql Commands like in the other answer or have create your own class to backup data.
But these are different mode of backup.
- Full Database Backup
- Differential Database Backup
- Transaction Log Backup
- Backup with Compression
But the disadvantage with this method is that it needs your sql management studio to be installed on your client system.
2 Comments
SqlConnection con = new SqlConnection();
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
con.ConnectionString = ConfigurationManager.ConnectionStrings["MyConString"].ConnectionString;
string backupDIR = "~/BackupDB";
string path = Server.MapPath(backupDIR);
try
{
var databaseName = "MyFirstDatabase";
con.Open();
string saveFileName = "HiteshBackup";
sqlcmd = new SqlCommand("backup database" +databaseName.BKSDatabaseName + "to disk='" + path + "\\" + saveFileName + ".Bak'", con);
sqlcmd.ExecuteNonQuery();
con.Close();
ViewBag.Success = "Backup database successfully";
return View("Create");
}
catch (Exception ex)
{
ViewBag.Error = "Error Occured During DB backup process !<br>" + ex.ToString();
return View("Create");
}
Comments
I have new method without SMO problems
1. Create .bat File with backup sqlcmd command
for backup
SqlCmd -E -S Server_Name –Q “BACKUP DATABASE [Name_of_Database] TO DISK=’X:PathToBackupLocation[Name_of_Database].bak'”
for restore
SqlCmd -E -S Server_Name –Q “RESTORE DATABASE [Name_of_Database] FROM DISK=’X:PathToBackupFile[File_Name].bak'”
2. Run the the bat file with WPF/C# code
FileInfo file = new FileInfo("DB\\batfile.bat");
Process process = new Process();
process.StartInfo.FileName = file.FullName;
process.StartInfo.Arguments = @"-X";
process.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
process.StartInfo.UseShellExecute = false; //Changed Line
process.StartInfo.RedirectStandardOutput = true; //Changed Line
process.Start();
string output = process.StandardOutput.ReadToEnd(); //Changed Line
process.WaitForExit(); //Moved Line
2 Comments
You can take database back-up of SQL server instance using C#, as below
Step 1: Install Nuget package "Install-Package Microsoft.SqlServer.SqlManagementObjects"
Step 2: Use the below C# Command to take backup using Custom function
public void BackupDatabase(string databaseName, string userName, string password, string serverName, string destinationPath)
{
//Define a Backup object variable.
Backup sqlBackup = new Backup();
//Specify the type of backup, the description, the name, and the database to be backed up.
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "BackUp of:" + databaseName + "on" + DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = "FullBackUp";
sqlBackup.Database = databaseName;
//Declare a BackupDeviceItem
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath + "FullBackUp.bak", DeviceType.File);
//Define Server connection
ServerConnection connection = new ServerConnection(serverName, userName, password); //To Avoid TimeOut Exception
Server sqlServer = new Server(connection);
sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
Database db = sqlServer.Databases[databaseName];
// Reference Database As microsoft.sqlserver.management.smo.database, not as System.entity.database
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
//Add the device to the Backup object.
sqlBackup.Devices.Add(deviceItem);
//Set the Incremental property to False to specify that this is a full database backup.
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
//Specify that the log must be truncated after the backup is complete.
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
//Run SqlBackup to perform the full database backup on the instance of SQL Server.
sqlBackup.SqlBackup(sqlServer);
//Remove the backup device from the Backup object.
sqlBackup.Devices.Remove(deviceItem);
}
Add References
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended
Microsoft.SqlServer.SqlEnum
That's it, you are done, it will take backup of specified database at specified location passed to the function.
Source: Various ways to back up SQL server database
Note: User must have proper rights to write backup data on specified disk location.
Comments
This worked for me...
private void BackupButtonClick(object sender, RoutedEventArgs e)
{
// FILE NAME WITH DATE DISTICNTION
string fileName = string.Format("SchoolBackup_{0}.bak", DateTime.Now.ToString("yyyy_MM_dd_h_mm_tt"));
try
{
// YOUR SEREVER OR MACHINE NAME
Server dbServer = new Server (new ServerConnection("DESKTOP"));
Microsoft.SqlServer.Management.Smo.Backup dbBackup = new Microsoft.SqlServer.Management.Smo.Backup()
{
Action = BackupActionType.Database,
Database = "School"
};
dbBackup.Devices.AddDevice(@backupDirectory() +"\\"+ fileName, DeviceType.File);
dbBackup.Initialize = true;
dbBackup.SqlBackupAsync(dbServer);
MessageBox.Show("Backup", "Backup Completed!");
}
catch(Exception err)
{
System.Windows.MessageBox.Show(err.ToString());
}
}
// THE DIRECTOTRY YOU WANT TO SAVE IN
public string backupDirectory()
{
using (var dialog = new FolderBrowserDialog())
{
var result = dialog.ShowDialog();
return dialog.SelectedPath;
}
}
Comments
You can use the following queries to Backup and Restore, you must change the path for your backup
Database name=[data]
Backup:
BACKUP DATABASE [data] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\data.bak' WITH NOFORMAT, NOINIT, NAME = N'data-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Restore:
RESTORE DATABASE [data] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\data.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
1 Comment
private void BackupManager_Load(object sender, EventArgs e)
{
txtFileName.Text = "DB_Backup_" + DateTime.Now.ToString("dd-MMM-yy");
}
private void btnDBBackup_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtFileName.Text.Trim()))
{
BackUp();
}
else
{
MessageBox.Show("Please Enter Backup File Name", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtFileName.Focus();
return;
}
}
private void BackUp()
{
try
{
progressBar1.Value = 0;
for (progressBar1.Value = 0; progressBar1.Value < 100; progressBar1.Value++)
{
}
pl.DbName = "Inventry";
pl.Path = @"D:/" + txtFileName.Text.Trim() + ".bak";
for (progressBar1.Value = 100; progressBar1.Value < 200; progressBar1.Value++)
{
}
bl.DbBackUp(pl);
for (progressBar1.Value = 200; progressBar1.Value < 300; progressBar1.Value++)
{
}
for (progressBar1.Value = 300; progressBar1.Value < 400; progressBar1.Value++)
{
}
for (progressBar1.Value = 400; progressBar1.Value < progressBar1.Maximum; progressBar1.Value++)
{
}
if (progressBar1.Value == progressBar1.Maximum)
{
MessageBox.Show("Backup Saved Successfully...!!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Action Failed, Please try again later", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show("Action Failed, Please try again later", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
progressBar1.Value = 0;
}
}