Introduction
Developers need to take backup and restore database. Database backup and restore can help you avert disaster. If you backup your files regularly, you can retrieve your information. By taking database backup and restoration through coding, so this could be done via Server Management Objects. So here, I will describe what is SMO and how it will be used for database backup and restoration.
SQL Server Management Objects (also called SMO) is a .NET library which allows you to access and manage all objects of the Microsoft SQL Server.SMO supports SQL Server 2000, 2005 and 2008, 2012. All functions available in SQL Server Management Studio are available in SMO but SMO includes several more features than Management Studio.
Background
You will have to create DSN for connection.
Before coding, you must set the reference to the SMO assembly. You need to add these components:
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
After Adding References, you need to add 2 using
statements:
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
Using 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();
cbservername.Properties.Items.Clear();
cmd = new OdbcCommand("select * from sys.databases", dbc.openconn());
dr = cmd.ExecuteReader();
while (dr.Read())
{
cbdatabasename.Properties.Items.Add(dr[0]);
}
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();
cmd = new OdbcCommand("select * from sys.servers", dbc.openconn());
dr = cmd.ExecuteReader();
while (dr.Read())
{
cbservername.Properties.Items.Add(dr[1]);
}
dr.Close();
}
Database Backup
public void blank(string str)
{
if (string.IsNullOrEmpty(cbservername.Text) | string.IsNullOrEmpty(cbdatabasename.Text))
{
XtraMessageBox.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)
{
query("Backup database " + cbdatabasename.Text +
" to disk='" + saveFileDiaog1.FileName + "'");
XtraMessageBox.Show("Database BackUp has been created successful.");
}
}
}
}
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();
running on that database and brings SQL Server database in a single user mode.
string Alter1 = @"ALTER DATABASE
[" + DatabaseFullPath + "] SET Single_User WITH Rollback Immediate";
OdbcCommand Alter1Cmd = new OdbcCommand(Alter1, sqlcon);
Alter1Cmd.ExecuteNonQuery();
string Restore = @"RESTORE DATABASE
[" + DatabaseFullPath + "] FROM DISK = N'" +
backUpPath + @"' WITH FILE = 1, NOUNLOAD, STATS = 10";
OdbcCommand RestoreCmd = new OdbcCommand(Restore, sqlcon);
RestoreCmd.ExecuteNonQuery();
string Alter2 = @"ALTER DATABASE
[" + DatabaseFullPath + "] SET Multi_User";
OdbcCommand Alter2Cmd = new OdbcCommand(Alter2, sqlcon);
Alter2Cmd.ExecuteNonQuery();
Cursor.Current = Cursors.Default;
}
}
Conclusion
This code uses the SQL Server 2005, 2012 backup/restore facility. The code follows the rules of SQL Server 2005, 2012 while backing up or restoring database.