C#实现数据库的备份与还原

***************************************数据库备份******************************
方法一：（需引用sqldmo.dll）
public static void DbBackup()
...{
    SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
    SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
    try
    ...{
        oSQLServer.LoginSecure = false;
        oSQLServer.Connect("localhost", "sa", "1234");
        oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
        oBackup.Database = "Northwind";
        oBackup.Files = @"d:Northwind.bak";
        oBackup.BackupSetName = "Northwind";
        oBackup.BackupSetDescription = "数据库备份";
        oBackup.Initialize = true;
        oBackup.SQLBackup(oSQLServer);
    }
    catch
    ...{
        throw;
    }
    finally
    ...{
        oSQLServer.DisConnect();
    }
}


方法二：
在事件里填写如下
SaveFileDialog sfd = new SaveFileDialog();
string backPath ="";
sfd.Filter= "*.bak|*.bak|*.back|*.back" ;
sfd.InitialDirectory=@"C:";
sfd.FilterIndex=1;
sfd.RestoreDirectory=true;
if(sfd.ShowDialog()==DialogResult.OK)
...{
    backPath = sfd.FileName.ToString();
    SqlConnection con =li.createCon();
    SqlCommand cmdBK = new SqlCommand();
    cmdBK.CommandType = CommandType.Text;
    cmdBK.Connection = con;
    cmdBK.CommandText = @"backup database GJJ to disk='"+backPath+"' with init";
    try 
    ...{
        con.Open();
        cmdBK.ExecuteNonQuery();
        MessageBox.Show("数据库备份成功","数据库备份",MessageBoxButtons.OK,MessageBoxIcon.Warning);
    }
    catch(Exception ex) 
    ...{
        MessageBox.Show(ex.Message);
    }
    finally
    ...{
        con.Close();
        con.Dispose();
    }
}

**************************************数据库还原********************************************
在使用这个方法之前先引用sqldmo.dll


还原数据库#region 还原数据库
private void btn_ReBack_Click(object sender, System.EventArgs e)
...{
    OpenFileDialog ofd = new OpenFileDialog();
    ofd.Filter= "*.bak|*.bak|*.back|*.back" ;
    ofd.FilterIndex=1;
    ofd.InitialDirectory=@"C:";
    if(ofd.ShowDialog()==DialogResult.OK)
    ...{
        string backuppath = ofd.FileName.ToString();
        this.DbRestore(backuppath);
    }
}

#endregion

用于数据库还原的方法#region 用于数据库还原的方法
public void DbRestore(string backuppath)
...{
    int Success=0;
    string DataBaseAddr = ConfigurationSettings.AppSettings["DatabaseAddr"];
    string DataBasePwd = ConfigurationSettings.AppSettings["Pwd"];
    string DataBaseUid = ConfigurationSettings.AppSettings["Uid"];
    SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
    SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
    try
    ...{
        do
        ...{
            oSQLServer.LoginSecure = false;
            oSQLServer.Connect(DataBaseAddr, DataBaseUid, DataBasePwd);
            oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
            oRestore.Database = "GJJ";
            oRestore.Files = backuppath;
            oRestore.FileNumber = 1;                
            oRestore.ReplaceDatabase = true;
            DialogResult result = MessageBox.Show("此操作将修改之前所有的数据。您确定要还原数据库吗？","警告",MessageBoxButtons.OKCancel,MessageBoxIcon.Warning);
            if(result==DialogResult.OK)
            ...{
                this.ExcuteProcedure();
                oRestore.SQLRestore(oSQLServer);

                Success=1;
                MessageBox.Show("数据库还原成功！","数据库还原",MessageBoxButtons.OK,MessageBoxIcon.Warning);
                break;
            }
            else
            ...{
                MessageBox.Show("未进行数据库还原！","数据库还原",MessageBoxButtons.OK,MessageBoxIcon.Warning);
            }
        }
        while(Success==0);
    }

    catch(Exception ex)
    ...{
        MessageBox.Show(ex.Message);
        MessageBox.Show("数据库还原失败！","数据库还原",MessageBoxButtons.OK,MessageBoxIcon.Warning);    
    }
    finally
    ...{
        oSQLServer.DisConnect();
    }
}
#endregion
        
调用killspid存储过程，杀掉进程(注意：此存储过程放在master库中，下面的连接字符串连接到此库)#region 调用killspid存储过程，杀掉进程(注意：此存储过程放在master库中，下面的连接字符串连接到此库)
private void ExcuteProcedure()
...{
    SqlConnection conn1 = new SqlConnection(ConfigurationSettings.AppSettings["LinkToMaster"]);
    SqlCommand cmd = new SqlCommand("killspid",conn1);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@dbname","GJJ");
    try
    ...{
        conn1.Open();
        cmd.ExecuteNonQuery();
    }
    catch(Exception ex)
    ...{
        MessageBox.Show(ex.Message);
    }
    finally
    ...{
        conn1.Close();
    }
}
#endregion