In this post I will create 2 voids, one we will use to create a backup for our sql server database, and the other void we will use to restore the backup, our database name is ‘Northwind’.
C# Code:
//add these classes to your project
using System.Data.SqlClient;
using System.IO;
VB Code:
Imports System.IO
Imports System.Data.SqlClient
C# Code:
//backup datebase, to use ità createBackup(“databasename”)
private void createBackup(string dbName)
{
//connection string (with master database) I read it from application properties
SqlConnection _conMasterDatabase = new SqlConnection(Properties.Settings.Default.con_dbMaster);
try
{
if (MessageBox.Show("Are you sure of this operation?", "Attention; please", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
SqlCommand _cmd = new SqlCommand();
_cmd.Connection = _conMasterDatabase;
//creating object from Save-file-dialog to save the backup file
SaveFileDialog _saveFile = new SaveFileDialog();
_saveFile.AddExtension = true;
_saveFile.DefaultExt = "bak";
_saveFile.Title = "Please; rename the backup file.";
_saveFile.Filter = "backup files (*.bak)|*.bak|All files (*.*)|*.*";
_saveFile.FilterIndex = 1;
if (_saveFile.ShowDialog() == DialogResult.OK)
{
//dynamic command text, dbName refers to my database name I want to create a backup from
_cmd.CommandText = "backup database " + dbName.ToString() + " to disk = '" + _saveFile.FileName + "'";
if (_conMasterDatabase.State == ConnectionState.Closed)
{
_conMasterDatabase.Open();
}
try
{
_cmd.ExecuteNonQuery();
MessageBox.Show("Operation succeeded; Please re-open your application if you want to use it now.", "Congratulation!");
}
catch (Exception exx)
{
MessageBox.Show(string.Format("{0} , {1}{2}", exx.Message.ToString(), "\n", "Operation did not succeed."), "Error");
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Error");
}
finally
{
if (_conMasterDatabase.State == ConnectionState.Open)
{
_conMasterDatabase.Close();
}
}
}
VB Code:
'create backup file, you can use it like this [createBackup("Northwind")]
Private Sub createBackup(ByVal dbName As String)
Dim conMasterdb As New SqlConnection(My.Settings.con_dbMaster.ToString())
If MessageBox.Show("Are you sure of this operation?", "Attention; please", MessageBoxButtons.OKCancel) = Windows.Forms.DialogResult.OK Then
Dim savefile As New SaveFileDialog()
savefile.AddExtension = True
savefile.DefaultExt = "bak"
savefile.Title = "Please; rename the backup file."
savefile.Filter = "backup files (*.bak)|*.bak|All files (*.*)|*.*"
savefile.FilterIndex = 1
If savefile.ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim cmd As New SqlCommand("backup database " + dbName.ToString() + " to disk = '" + savefile.FileName + "'", conMasterdb)
If conMasterdb.State = ConnectionState.Closed Then
conMasterdb.Open()
End If
Try
cmd.ExecuteNonQuery()
MessageBox.Show("Operation succeeded; Please re-open your application if you want to use it now.", "Congratulation!")
Catch ex As Exception
MessageBox.Show(ex.Message.ToString() & vbCrLf & "Operation did not succeed." & "Error")
Finally
If conMasterdb.State = ConnectionState.Open Then
conMasterdb.Close()
End If
End Try
End If
End If
End Sub
C# Code:
//restore database, you can call it easily restoreBackup(“databasename”)
private void restoreBackup(string dbName)
{
//connection string (with master database) I read it from application properties
SqlConnection _conMasterDatabase = new SqlConnection(Properties.Settings.Default.con_dbMaster);
try
{
//create object from open-file-dialog to select a backup file which I want to restore
OpenFileDialog _ofDialog = new OpenFileDialog();
_ofDialog.AddExtension = true;
_ofDialog.DefaultExt = "bak";
_ofDialog.Title = "Select backup file to restore";
_ofDialog.Filter = "backup files (*.bak)|*.bak|All files (*.*)|*.*";
_ofDialog.FilterIndex = 1;
if (_ofDialog.ShowDialog() == DialogResult.OK)
{
if (_conMasterDatabase.State == ConnectionState.Closed)
{
_conMasterDatabase.Open();
}
string _bakFilePath = _ofDialog.FileName;
SqlCommand _cmd = new SqlCommand(string.Format("alter database {0} set single_user with rollback immediate;exec sp_dboption {1} ,'offline' ,'TRUE';restore database {2} from disk = '{3}' with REPLACE", dbName.ToString(), dbName.ToString(), dbName.ToString(), _bakFilePath), _conMasterDatabase);
_cmd.ExecuteNonQuery();
MessageBox.Show("Operation succeeded,Please re-open the application.", "Attention!");
}
}
catch (Exception exx)
{
MessageBox.Show(string.Format("{0} , {1}{2}", exx.Message.ToString(), "\n", "Operation did not succeed."), "Error");
}
finally
{
if (_conMasterDatabase.State == ConnectionState.Open)
{
_conMasterDatabase.Close();
}
}
}
VB Code:
'restore backup, you can use it like this [restoreBackup("Northwind")]
Private Sub restoreBackup(ByVal dbName As String)
Dim conMasterdb As New SqlConnection(My.Settings.con_dbMaster.ToString())
Dim ofDialog As New OpenFileDialog()
ofDialog.AddExtension = True
ofDialog.DefaultExt = "bak"
ofDialog.Title = "Select backup file to restore"
ofDialog.Filter = "backup files (*.bak)|*.bak|All files (*.*)|*.*"
ofDialog.FilterIndex = 1
If ofDialog.ShowDialog() = DialogResult.OK Then
Dim bakFilePath As String = ofDialog.FileName
If conMasterdb.State = ConnectionState.Closed Then
conMasterdb.Open()
End If
Dim cmd As New SqlCommand(String.Format("alter database {0} set single_user with rollback immediate;exec sp_dboption {1} ,'offline' ,'TRUE';restore database {2} from disk = '{3}' with REPLACE", dbName.ToString(), dbName.ToString(), dbName.ToString(), bakFilePath), conMasterdb)
Try
cmd.ExecuteNonQuery()
MessageBox.Show("Operation succeeded,Please re-open the application.", "Congratulation!")
Catch ex As Exception
MessageBox.Show(ex.Message.ToString() & vbCrLf & "Operation did not succeed." & "Error")
Finally
If conMasterdb.State = ConnectionState.Open Then
conMasterdb.Close()
End If
End Try
End If
End Sub
