Asp.net C # Two Methods of database backup and restoration (SQL server)

Source: Internet
Author: User
Tags mssql server

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;
}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.