Wpf c # notes in using sqldmo for database backup and Restoration during development

First download the sqldmo file package: sqldmo

 

After the download, decompress the package and perform the following operations:

Step 1: first set msvcr71.dll and sqldmo. DLL, resources/2052/sqldmo. rll, resources/1033/sqldmo. copy rll to the C:/program files/Microsoft SQL Server/80/tools/binn directory.

Step 2: open and start. Enter regsvr32 "C:/program files/Microsoft SQL Server/80/tools/binn/sqldmo. dll" to register the sqldmo. dll file.

 

 

If the following error persists after the preceding two operations:

Retrieving the com class factory for component with clsid {10020200-e260-11cf-ae68-00aa004a34d5} failed due to the following error: 80070005.

Note: The C:/program files/folder only has the Administrator and system control permissions, but does not have the permissions of any other users. Therefore, we add the network service read permission to the Microsoft SQL Server folder.

 

Even after registration, an error is reported in the running program, similar to the following information:

"Event invocation for COM objects requires event to be attributed with dispidattri"

This is because after the sqldmo. dll file is referenced in the project bin, the default value of "embedded interoperability type" in the DLL attribute is true. modify it to false.

 

The following is the primary code for database backup and Restoration:

---------------------------------- Data backup ------------------------------------------------

Private void btnsave_click (Object sender, routedeventargs e) {// backup savefiledialog = new savefiledialog (); savefiledialog. filter = "(*. MDF) | *. MDF "; savefiledialog. filterindex = 0; If (savefiledialog. showdialog () = true) {string filepath = savefiledialog. filename; sqlback (Global. db_source, global. db_uid, global. db_upwd, global. db_name, filepath) ;}# region sqldatabase backup function // <Summary> // SQL database backup // </Summary> /// <Param name = "serverip"> SQL Server IP address or (localhost) </param> /// <Param name = "loginusername"> database logon name </param> /// <Param name = "loginpass"> database logon password </param>/ // <Param name = "dbname"> database name </param> // <Param name = "backpath"> backup path </param> Public void sqlback (string serverip, string loginusername, string loginpass, string dbname, string backpath) {sqldmo. backup obackup = New sqldmo. backup (); sqldmo. sqlserver osqlserver = new sqldmo. sqlserver (); obackup. action = 0; obackup. initialize = true; sqldmo. backupsink_percentcompleteeventhandler pceh = new sqldmo. backupsink_percentcompleteeventhandler (STEP); obackup. percentcomplete + = pceh; try {osqlserver. loginsecure = false; osqlserver. connect (serverip, loginusername, loginpass); obackup. action = sqldmo. sqldmo_backup _ Type. sqldmobackup_database; obackup. database = dbname; obackup. files = @ "" + backpath + ""; obackup. backupsetname = dbname; obackup. backupsetdescription = "database backup"; obackup. initialize = true; obackup. sqlbackup (osqlserver); MessageBox. show ("backup successful! ");} Catch (exception ex) {MessageBox. show ("backup failed because:" + ex. message);} finally {osqlserver. disconnect () ;}} private void step (string message, int percent) {This. pb. value = percent ;}

 

-------------------------------- Data restoration ----------------------------------------------------------

Private Static datarestore _ instance = NULL; // page instance public datarestore () {initializecomponent ();} public static datarestore instance () {If (_ instance = NULL) {_ instance = new datarestore ();} else {MessageBox. show ("an instance is running! ");} Return _ instance;} // <summary> // obtain the Database Server LIST /// </Summary> private void getsqlserverlist () {sqldmo. _ application sqlapp = new sqldmo. application (); sqldmo. namelist sqlservers = sqlapp. listavailablesqlservers (); For (INT I = 0; I <sqlservers. count; I ++) {object SRV = sqlservers. item (I + 1); If (SRV! = NULL) {// This. cboservers. items. add (SRV) ;}// if (this. cboservers. items. count> 0) // This. cboservers. selectedindex = 0; // else // This. cboservers. TEXT = "<no available SQL servers>";} private void btnsave_click (Object sender, routedeventargs e) {// restore openfiledialog = new openfiledialog (); openfiledialog. filter = "(*. MDF) | *. MDF "; openfiledialog. filterindex = 0; If (openfiledia Log. showdialog () = true) {string filepath = openfiledialog. filename; // obtain the selected backup file and perform the Restoration Operation sqldbrestore (Global. db_source, global. db_uid, global. db_upwd, global. db_name, filepath );}} # region SQL Restore database // <summary> // SQL Restore database // </Summary> // <Param name = "serverip"> SQL Server IP address or (localhost) </param> /// <Param name = "loginusername"> database logon name </param> /// <Param name = "loginpass"> database logon password </param>/ // <Param name = "dbname"> name of the database to be restored </param> // <Param name = "backpath"> path of the Database Backup </param> Public void sqldbrestore (string serverip, string loginusername, string loginpass, string dbname, string backpath) {sqldmo. restore orestore = new sqldmo. restore (); sqldmo. sqlserver osqlserver = new sqldmo. sqlserver (); orestore. action = 0; // sqldmo. restoresink_percentcompleteeventhandler pceh = new sqldmo. restore Sink_percentcompleteeventhandler (STEP); // orestore. percentcomplete + = pceh; try {osqlserver. connect (serverip, loginusername, loginpass); sqldmo. queryresults QR = osqlserver. enumprocesses (-1); int icolpidnum =-1; int icoldbname =-1; // kill other connection processes for (INT I = 1; I <= QR. columns; I ++) {string strname = QR. get_columnname (I); If (strname. toupper (). trim () = "spid") {icolpidnum = I;} else if (strna Me. toupper (). Trim () = "dbname") {icoldbname = I;} If (icolpidnum! =-1 & icoldbname! =-1) break;} For (INT I = 1; I <= QR. rows; I ++) {int LPID = QR. getcolumnlong (I, icolpidnum); string strdbname = QR. getcolumnstring (I, icoldbname); If (strdbname. toupper () = "jcwzdb ". toupper () {osqlserver. killprocess (LPID) ;}} orestore. action = sqldmo. sqldmo_restore_type.sqldmorestore_database; orestore. database = dbname; orestore. files = backpath; orestore. filenumber = 1; orestore. replacedat Abase = true; orestore. sqlrestore (osqlserver); MessageBox. Show ("Data restored successfully! ");} Catch (system. exception ex) {MessageBox. show ("data restoration failed:" + ex. tostring ();} finally {osqlserver. disconnect () ;}} private void step (string message, int percent) {This. pb. value = percent;} # endregion