C#中结合使用SQLDMO实现备份、还原SQLserver数据库

最新推荐文章于 2020-09-07 17:32:58 发布

forlinux

最新推荐文章于 2020-09-07 17:32:58 发布

阅读量3k

点赞数

分类专栏： .NET/windows 数据库技术文章标签： sqlserver c# object string button 数据库

本文链接：https://blog.csdn.net/forlinux/article/details/1428213

版权

.NET/windows 同时被 2 个专栏收录

52 篇文章 1 订阅

订阅专栏

数据库技术

10 篇文章 0 订阅

订阅专栏

//－－－DBTools.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Magicbit.Framework
{
     public partial class DBTools : Form
    {

         private static DBTools _Instance = null ;
         public static DBTools Instance()
        {
             if (_Instance == null )
            {
                _Instance = new DBTools();
            }
             else
            {
                MessageBox.Show( " 已经有一个实例在运行！ " );
            }
             return _Instance;
        }


         public DBTools()
        {
            InitializeComponent();
        }

         private void BackAndRecoverDB_Load( object sender, EventArgs e)
        {
             this .txtSavePath.Text = Application.StartupPath;
             // this.GetSQLServerList();
        }
         private void GetSQLServerList()
        {
             // get all available SQL Servers
            SQLDMO._Application sqlApp = new SQLDMO.ApplicationClass();
            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 GetBackUpDB()
        {
            SQLDMO.Application sqlApp    = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer srv         = new SQLDMO.SQLServer();
            srv.Connect( this .cboServers.Text.Trim(), this .txtUserName.Text.Trim(), this .txtPassword.Text.Trim());
             foreach (SQLDMO.Database db in srv.Databases)
            {
                 if (db.Name != null )
                     this .cboDatabase.Items.Add(db.Name);
            }

        }
         private void pictureBox1_Click( object sender, EventArgs e)
        {
            MessageBox.Show( " 欢迎使用数据库备份、还原工具，本工具将协助你备份和还原数据库，确保数据安全! " , " 备份您的数据库 " );
        }

         private void button1_Click( object sender, EventArgs e)
        {
             this .GetBackUpDB();
        }
         private void BackUpDB()
        {

             string selfName = this .txtSavePath.Text.Trim() + @" " + this .cboDatabase.Text.Trim() + " _ " + System.DateTime.Now.ToString( " yyyyMMddHHmmss " ) + " .DAT " ;
             string deviceName = this .cboDatabase.Text.Trim() + " bak " ;
             string remark = " 数据备份 " ;

             // BACKUP DB
            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            oBackup.Action = 0 ;
            oBackup.Initialize = true ;
            SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
            oBackup.PercentComplete += pceh;
             try
            {
                oSQLServer.LoginSecure = false ;
                oSQLServer.Connect( this .cboServers.Text.Trim(), this .txtUserName.Text.Trim(), this .txtPassword.Text.Trim());
                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                oBackup.Database = this .cboDatabase.Text.Trim(); // 数据库名
                oBackup.Files = selfName; // 文件路径
                oBackup.BackupSetName = deviceName; // 备份名称
                oBackup.BackupSetDescription = remark; // 备份描述
                oBackup.Initialize = true ;
                oBackup.SQLBackup(oSQLServer);

            }
             catch (System.Exception ex)
            {
                MessageBox.Show( " 数据备份失败： " + ex.ToString());
            }
             finally
            {
                oSQLServer.DisConnect();
            }
        }
         private void Step( string message, int percent)
        {
             this .progressBar1.Value = percent;
        }
         private void button2_Click( object sender, EventArgs e)
        {
             this .Cursor = Cursors.WaitCursor;
             this .label6.Visible = true ;
             this .progressBar1.Visible = true ;
             this .BackUpDB();
             this .Cursor = Cursors.Default;
             this .label6.Text = " 备份已完成. " ;
        }

         public void RestoreDB()
        {
             string filePath = this .txtBackUpFile.Text.Trim();
            SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            oRestore.Action = 0 ;
            SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
            oRestore.PercentComplete += pceh;
             try
            {
            oSQLServer.Connect( this .cboServers.Text.Trim(), this .txtUserName.Text.Trim(), this .txtPassword.Text.Trim());
            SQLDMO.QueryResults qr = oSQLServer.EnumProcesses( - 1 ) ;
             int iColPIDNum = - 1 ;
             int iColDbName = - 1 ;
             // 杀死其它的连接进程
             for ( int i = 1 ;i <= qr.Columns;i ++ )
            {
                 string strName = qr.get_ColumnName(i) ;
                 if (strName.ToUpper().Trim() == " SPID " )
                {
                     iColPIDNum = i ;
                }
                 else if (strName.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() == " CgRecord " .ToUpper())
                    oSQLServer.KillProcess(lPID) ;
                }

                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                oRestore.Database = this .cboDBtoBackup.Text;
                oRestore.Files = filePath;
                oRestore.FileNumber = 1 ;
                oRestore.ReplaceDatabase = true ;
                oRestore.SQLRestore(oSQLServer);

            }
             catch (System.Exception ex)
            {
                MessageBox.Show( " 数据还原失败： " + ex.ToString());
            }
             finally
            {
                oSQLServer.DisConnect();
            }


        }

         private void button3_Click( object sender, EventArgs e)
        {
             this .folderBrowserDialog1.Description = " 请选择备份文件存放目录 " ;
             this .folderBrowserDialog1.ShowNewFolderButton = true ;
             this .folderBrowserDialog1.ShowDialog();
             this .txtSavePath.Text = this .folderBrowserDialog1.SelectedPath;
        }

         private void button4_Click( object sender, EventArgs e)
        {
             this .openFileDialog1.DefaultExt = " *.dat " ;
             this .openFileDialog1.Title = " 请选择要还原的数据库备份文件. " ;
             this .openFileDialog1.ShowDialog();
             this .txtBackUpFile.Text = this .openFileDialog1.FileName;
        }

         private void button5_Click( object sender, EventArgs e)
        {
             this .Cursor = Cursors.WaitCursor;
             this .label6.Visible = true ;
             this .progressBar1.Visible = true ;
             this .RestoreDB();
             this .Cursor = Cursors.Default;
             this .label6.Text = " 还原已完成. " ;
        }



    }
}