使用.net备份和还原数据库

 

使用.net备份和还原数据库
C#实现SQLSERVER2000数据库备份还原的两种方法
: 方法一（不使用SQLDMO）：

///
///备份方法
///
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.ExecuteNonQuery();
MessageBox.Show("Backup successed.");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
conn.Dispose();
}


///
///还原方法
///
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.ExecuteReader();
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.ExecuteNonQuery();
}

SqlCommand cmdRT = new SqlCommand();
cmdRT.CommandType = CommandType.Text;
cmdRT.Connection = conn;
cmdRT.CommandText = @"restore database test from disk='C:\ba'";

try
{
cmdRT.ExecuteNonQuery();
MessageBox.Show("Restore successed.");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}





方法二（使用SQLDMO）：

///
///备份方法
///
SQLDMO.Backup backup = new SQLDMO.BackupClass();
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
//显示进度条
SQLDMO.BackupSink_PercentCompleteEventHandler progress = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
backup.PercentComplete += progress;

try
{
server.LoginSecure = false;
server.Connect(".", "sa", "sa");
backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
backup.Database = "test";
backup.Files = @"D:\test\myProg\backupTest";
backup.BackupSetName = "test";
backup.BackupSetDescription = "Backup the database of test";
backup.Initialize = true;
backup.SQLBackup(server);
MessageBox.Show("Backup successed.");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
server.DisConnect();
}
this.pbDB.Value = 0;


///
///还原方法
///
SQLDMO.Restore restore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
//显示进度条
SQLDMO.RestoreSink_PercentCompleteEventHandler progress = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
restore.PercentComplete += progress;

//KILL DataBase Process
SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'", conn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
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.ExecuteNonQuery();
}
conn.Close();

try
{
server.LoginSecure = false;
server.Connect(".", "sa", "sa");
restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
restore.Database = "test";
restore.Files = @"D:\test\myProg\backupTest";
restore.FileNumber = 1;
restore.ReplaceDatabase = true;
restore.SQLRestore(server);
MessageBox.Show("Restore successed.");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
server.DisConnect();
}
this.pbDB.Value = 0;

   （ 用SQL语句实现对数据库备份还原操作 

      备份SqlServer数据库: 
　　backup database 数据库名 to disk (备份文件存放路径+文件名).bak 

　　还原SqlServer数据库: 
　　string path = this.FileUpload1.PostedFile.FileName; //获得备份路径及数据库名称 
　　use master restore database 数据库名 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 数据库备份 
{ 
    public partial class Form1 : Form 
    { 
        //需要添加一个引用C:\Program Files\Microsoft SQL Server\80\Tools\Binn下的SQLDMO.DLL； 
        //SQL默认安装下是在以上路径 
        //备份的文件放在C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP下 
        string ServerName = ""; 
        string UserName = "sa";//暂时锁定，可以根据需要自己设置 
        string Password = "sa"; 
      
        public Form1() 
        { 
            InitializeComponent(); 
            
        } 
        //获取服务器列表 
        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("取数据库服务器列表出错：" + e.Message)); 
            } 
            finally 
            { 
                sqlApp.Quit(); 
            } 
            return alServers; 
        } 
        //获取数据库列表 
        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.SQLServer 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("连接数据库出错：" + e.Message); 
            } 
            finally 
            { 
                svr.DisConnect(); 
                sqlApp.Quit(); 
            } 
            return alDbs; 
        } 
        //备份数据 
        public bool BackUPDB(string strDbName, string strFileName, ProgressBar pgbMain) 
        { 
            ProgressBar PBar = pgbMain; 
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass(); 
            try 
            { 
                svr.Connect(ServerName, UserName, Password); 
                SQLDMO.Backup bak = new SQLDMO.BackupClass(); 
                bak.Action = 0; 
                bak.Initialize = true; 
                SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step); 
                bak.PercentComplete += pceh; 
                bak.Files = strFileName;//这里可以写成路径+文件名形式，自己写！ 
                bak.Database = strDbName; 
                bak.SQLBackup(svr); 
                return true; 
            } 
            catch (Exception err) 
            { 
                throw (new Exception("备份数据库失败" + err.Message)); 
            } 
            finally 
            { 
                svr.DisConnect(); 
            } 
        } 
        //进度条 
        private void Step(string message, int percent) 
        { 
            PBar.Visible = true; 
            PBar.Value = percent; 
        } 

借花献佛 

C# code

（ 用SQL语句实现对数据库备份还原操作 

备份SqlServer数据库: 
　　backup database 数据库名 to disk (备份文件存放路径+文件名).bak 

　　还原SqlServer数据库: 
　　string path = this.FileUpload1.PostedFile.FileName; //获得备份路径及数据库名称 
　　use master restore database 数据库名 from disk='" + path + "'"; ） 


using System; 
using System.Collections.…



接上

C# code
    //还原数据
        public bool RestoreDB(string strDbName, string strFileName, ProgressBar pgbMain)
        {
            ProgressBar PBar = pgbMain;
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.QueryResults qr = svr.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() == strDbName.ToUpper())
                        svr.KillProcess(lPID);
                }

                SQLDMO.Restore res = new SQLDMO.RestoreClass();
                res.Action = 0;
                SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
                res.PercentComplete += pceh;
                res.Files = strFileName;//可以写成路径+文件名

                res.Database = strDbName;
                res.ReplaceDatabase = true;
                res.SQLRestore(svr);
                return true;
            }
            catch (Exception err)
            {
                throw (new Exception("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message));
            }
            finally
            {
                svr.DisConnect();
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            GetServerList();
            string ServerName = "(local)";//这里根据需要自己可以设置成动态的
            GetDbList(ServerName, UserName, Password);
        }
        //根据选择的服务器，列出数据库
        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {    //这里可以动态设置服务器名，角色名，密码
            //string ServerName = listBox1.SelectedItem.ToString();
            //GetDbList(ServerName, UserName, Password);
           
        }

        private void SelectPath_Click(object sender, EventArgs e)
        { 
            //选择保存路径
            //if (folderBrowserDialog1.ShowDialog(this) == DialogResult.OK)
            //{
            //    string path = folderBrowserDialog1.SelectedPath + "\\";
            //    txtPath.Text = path;
               
            //}
            //else
            //{
            //   folderBrowserDialog1.Dispose();
            //}
        }
        //备份按钮
        private void btnSave_Click(object sender, EventArgs e)
        {
            string strDbName = "db_bookmanage";
            txtSaveName.Text = DateTime.Now.ToString("yyyyMMddhhmmss");
            string strFileName = txtSaveName.Text.ToString().Trim();
            if (BackUPDB(strDbName, strFileName, PBar))
            {
                PBar.Visible = false;
                MessageBox.Show("备份完成！");
            }
           
        }
        //查看历史备份按钮
        private void button2_Click(object sender, EventArgs e)
        {
            listBox3.Items.Clear();
            string[] files = Directory.GetFiles(@"C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP");
            foreach (string file in files)
                listBox3.Items.Add(file.Substring(file.LastIndexOf("\\") + 1, file.Length - file.LastIndexOf("\\") - 1));
        }
        //还原数据按钮
        private void button1_Click(object sender, EventArgs e)
        {
            PBar.Visible = true;
            string strDbName = "db_bookmanage";
            //txtSaveName.Text = DateTime.Now.ToString("yyyyMMddhhmmss");
            if (txtReName.Text == "")
            {
                MessageBox.Show("请选择一个要还原的文件！");
                return;
            }
            string strFileName = txtReName.Text.ToString().Trim();
            if (RestoreDB(strDbName, strFileName, PBar))
            {
                PBar.Visible = false;
                MessageBox.Show("还原完成！");
            }
        }
        //历史备份文件名
        private void listBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            txtReName.Text = listBox3.SelectedItem.ToString();
        }  
        
    }
}

引用Interop.SQLDMO.dll后的注意事项。 
  SQLDMO.dll是个好东西，ASP.NET利用它可以实现在线备份、还原数据库等各种功能。近日有客户要求为其在后台添加一个管理数据库的功能。于是就出现了这篇文章。
     由于客户的数据库和WEB服务不再同一台服务器，当我们把网站部署在服务器上以后，运行程序，提示如下错误：


Retrieving the COM class factory for component with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80040154. 
而客户又不想在这台电脑安装MSSQL，所以我们只需要在没有安装MSSQL的电脑上注册SQLDMO.DLL组件。
第一步：首先将msvcr71.dll,  SQLDMO.DLL, Resources\2052\sqldmo.rll,Resources\1033\sqldmo.rll 拷贝到C:\Program Files\Microsoft SQL Server\80\Tools\Binn目录。
下载SQLDMO文件   
第二步：打开开始，在运行中输入 regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll" 注册sqldmo.dll。

正常情况下，经过以上两个步骤，网页就应该可以访问了的。 但是我们经过以上两次操作后，访问网页依然提示如下错误：

Retrieving the COM class factory for component with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80070005.后经过一段时间的检查，我们发现C:\Program Files\文件夹仅有Administrator和System的控制权限，而没有其他任何用户的权限，因此我们为Microsoft SQL Server文件夹增加上Network Service 的读取权限。
至此，问题得到解决！