Asp tutorial. net c # Database tutorial backup and restoration methods (SQL server)
The following describes how to use c # to back up the mssql server database. See the code below,
/// Backup method
///
Sqlconnection conn = new sqlconnection ("server =.; database = master; user id = sa; password = sa ;");
Sqlcommand cmdbk = new sqlcommand ();
Cmdbk. commandtype = commandtype. text;
Cmdbk. connection = conn;
Cmdbk. commandtext = @ "backup database test to disk = 'C: Ba' with init ";
Try
{
Conn. open ();
Cmdbk.exe cutenonquery ();
Messagebox. show ("backup successed .");
}
Catch (exception ex)
{
Messagebox. show (ex. message );
}
Finally
{
Conn. close ();
Conn. dispose ();
}
///
/// Restore Method
///
Sqlconnection conn = new sqlconnection ("server =.; database = master; user id = sa; password = sa; trusted_connection = false ");
Conn. open ();
// Kill database process
Sqlcommand cmd = new sqlcommand ("select spid from sysprocesses, sysdatabases where sysprocesses. dbid = sysdatabases. dbid and sysdatabases. name = 'test'", conn );
Sqldatareader dr;
Dr = cmd.exe cutereader ();
Arraylist list = new arraylist ();
While (dr. read ())
{
List. add (dr. getint16 (0 ));
}
Dr. close ();
For (int I = 0; I <list. count; I ++)
{
Cmd = new sqlcommand (string. format ("kill {0}", list), conn );
Cmd.exe cutenonquery ();
}
Sqlcommand cmdrt = new sqlcommand ();
Cmdrt. commandtype = commandtype. text;
Cmdrt. connection = conn;
Cmdrt. commandtext = @ "restore database test from disk = 'C: Ba '";
Try
{
Optional rt.exe cutenonquery ();
Messagebox. show ("restore successed .");
}
Catch (exception ex)
{
Messagebox. show (ex. message );
}
Finally
{
Conn. close ();
}
Method 2
(Restore database backup using SQL statements
Back up the sqlserver database:
Backup database name to disk (backup file storage path + file name). bak
Restore the sqlserver database:
String path = this. fileupload1.postedfile. filename; // obtain the backup path and database name.
Use master restore database name from disk = '"+ path + "'";)
Using system;
Using system. collections. generic;
Using system. componentmodel;
Using system. data;
Using system. drawing;
Using system. text;
Using system. windows. forms;
Using system. collections;
Using system. data. SQL;
Using system. io;
Namespace Database Backup
{
Public partial class form1: form
{
// Add a reference to sqldmo. dll under c: program filesmicrosoft SQL server80toolsbinn;
// By default, SQL is installed in the above path
// Put the backup file under c: program filesmicrosoft SQL servermssqlbackup
String servername = "";
String username = "sa"; // temporarily locked. You can set it as needed.
String password = "sa ";
Public form1 ()
{
Initializecomponent ();
}
// Obtain the Server LIST
Public arraylist getserverlist ()
{
Arraylist alservers = new arraylist ();
Sqldmo. application sqlapp = new sqldmo. applicationclass ();
Try
{
Sqldmo. namelist serverlist = sqlapp. listavailablesqlservers ();
For (int I = 1; I <= serverlist. count; I ++)
{
Alservers. add (serverlist. item (I ));
// Combobox1.items. add (serverlist. item (I ));
Listbox1.items. add (serverlist. item (I ));
}
}
Catch (exception e)
{
Throw (new exception ("An error occurred while retrieving the Database Server LIST:" + e. message ));
}
Finally
{
Sqlapp. quit ();
}
Return alservers;
}
// Obtain the Database List
Public arraylist getdblist (string strservername, string strusername, string strpwd)
{
String servername = strservername;
String username = strusername;
String password = strpwd;
Arraylist aldbs = new arraylist ();
Sqldmo. application sqlapp = new sqldmo. applicationclass ();
Sqldmo. svr = new sqldmo. sqlserverclass ();
Try
{
Svr. connect (servername, username, password );
Foreach (sqldmo. database db in svr. databases)
{
If (db. name! = Null)
Aldbs. add (db. name );
Listbox2.items. add (db. name );
}
}
Catch (exception e)
{
Messagebox. show ("database connection error:" + e. message );
}
Finally
{
Svr. disconnect ();
Sqlapp. quit ();
}
Return aldbs;
}
// Back up data
Public bool backupdb (string strdbname, string strfilename, progressbar pgbmain)
{
Progressbar pbar = pgbmain;
Sqldmo. svr = new sqldmo. sqlserverclass ();
Try
{
Svr. connect (servername, username, password );
Sqldmo. backup bak = new sqldmo. backupclass ();
Bak. action = 0;
Bak. initialize = true;
Sqldmo. backups tutorial ink_percentcompleteeventhandler pceh = new sqldmo. backupsink_percentcompleteeventhandler (step );
Bak. percentcomplete + = pceh;
Bak. files = strfilename; // you can write it as a path + file name!
Bak. database = strdbname;
Bak. sqlbackup (svr );
Return true;
}
Catch (exception err)
{
Throw (new exception ("failed to back up the database" + err. message ));
}
Finally
{
Svr. disconnect ();
}
}
// Progress bar
Private void step (string message, int percent)
{
Pbar. visible = true;
Pbar. value = percent;
}