Oracle Enterprise Manager OEM in LINUX
OEM Configuration and Installation in details
Loading...
SQL Server Database Backup and Restore in C# .NET
Before coding, you must set the reference to the SMO assembly. You need to add these components:
Microsoft.SqlServer.SmoMicrosoft.SqlServer.SmoExtendedMicrosoft.SqlServer.ConnectionInfoMicrosoft.SqlServer.Management.Sdk.Sfc
After Adding References, you need to add 2
using statements:using Microsoft.SqlServer.Management.Smo;using Microsoft.SqlServer.Management.Common;The Code
The following code creates connection with SQL Server. To execute:
"select * from sys.databases"The above query retrieves all databases from SQL Server.
public void Createconnection() {
DBbackup.DataBaseClass dbc = new DataBaseClass();
cbdatabasename.Items.Clear(); // select * from sys.databases getting all database name from sql server cmd = new OdbcCommand("select * from sys.databases", dbc.openconn()); dr = cmd.ExecuteReader(); while (dr.Read()) { cbdatabasename.Items.Add(dr[0].ToString()); } dr.Close(); }The following code gets server names that exist. To execute:
"select * from sys.servers"The above query retrieves servers:
public void serverName(){
DBbackup.DataBaseClass dbc = new DataBaseClass(); // select * from sys.servers getting server names that exist cmd = new OdbcCommand("select * from sys.servers", dbc.openconn()); dr = cmd.ExecuteReader(); while (dr.Read()) {
cbservername.Items.Add(dr[1].ToString()); } dr.Close();}Database Backup
public void backup(string str) { if (string.IsNullOrEmpty(cbservername.Text) | string.IsNullOrEmpty(cbdatabasename.Text)) {
MessageBox.Show("Server Name & Database can not be Blank"); return; } else { if (str == "backup") { saveFileDialog1.Filter = "Text files (*.bak)|*.bak|All files (*.*)|*.*"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) {
// the below query get backup of database you specified in combobox query("Backup database " + cbdatabasename.Text + " to disk='" + saveFileDiaog1.FileName + "'");
MessageBox.Show("Database BackUp has been created successfully."); } }
} }Database Restore
public void Restore(OdbcConnection sqlcon, string DatabaseFullPath, string backUpPath) {
using (sqlcon) { string UseMaster = "USE master"; OdbcCommand UseMasterCommand = new OdbcCommand(UseMaster, sqlcon); UseMasterCommand.ExecuteNonQuery(); // The below query will rollback any transaction which is running on database string Alter1 = @"ALTER DATABASE [" + DatabaseFullPath + "] SET Single_User WITH Rollback Immediate"; OdbcCommand Alter1Cmd = new OdbcCommand(Alter1, sqlcon); Alter1Cmd.ExecuteNonQuery(); // The below query will restore database file from disk where backup was //taken .... string Restore = @"RESTORE DATABASE [" + DatabaseFullPath + "] FROM DISK = N'" + backUpPath + @"' WITH FILE = 1, NOUNLOAD, STATS = 10"; OdbcCommand RestoreCmd = new OdbcCommand(Restore, sqlcon); RestoreCmd.ExecuteNonQuery(); // the below query change the database back to multiuser string Alter2 = @"ALTER DATABASE [" + DatabaseFullPath + "] SET Multi_User"; OdbcCommand Alter2Cmd = new OdbcCommand(Alter2, sqlcon); Alter2Cmd.ExecuteNonQuery(); Cursor.Current = Cursors.Default; }
}Using of Code:
create new project (C#) :
- --- In Designer Mode:
Add new form --> add 2 Combobox (cbservername , cbdatabasename).
Add 2 buttons (backupbtn , restorebtn)
2.--- In the Code Behind of form :
- in form_load event:
private void Form1_Load(object sender, EventArgs e)
{
serverName();
}
- in cbservername_SelectedIndexChanged event write:
private void cbservername_SelectedIndexChanged(object sender, EventArgs e)
{
Createconnection();
}
- in backupbtn_click event:
private void backupbtn_Click(object sender, EventArgs e)
{
backup("backup");
}
- in restorebtn_click event:
private void restorebtn_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(cbservername.Text) | string.IsNullOrEmpty(cbdatabasename.Text))
{
MessageBox.Show("Server Name & Database can not be Blank", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
openFileDialog1.Filter = "Text files (*.bak)|*.bak|All files (*.*)|*.*";
DBbackup.DataBaseClass dbc = new DataBaseClass();
Restore(dbc.openconn(), cbdatabasename.SelectedItem.ToString(), openFileDialog1.FileName);
MessageBox.Show("Database Backup file has been restore successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
