C#备份数据库

最新推荐文章于 2022-08-03 23:06:39 发布

初吻给了烟

最新推荐文章于 2022-08-03 23:06:39 发布

阅读量2.1k

点赞数

CC 4.0 BY-SA版权

文章标签： c# 数据库 string database microsoft sql server

本文链接：https://blog.youkuaiyun.com/zhlu880516/article/details/5492360

SQLDMO(SQL Distributed Management Objects，SQL分布式管理对象)封装了Microsoft SQL Server数据库中的对象。SQLDMO是Microsoft SQL Server中企业管理器所使用的应用程序接口，所以它可以执行很多功能，其中当然也包括对数据库的备份和恢复。
当然要用到 sqldmo 来做这个工作
找到 sqldmo.dll这个文件
C:/Program Files/Microsoft SQL Server/80/Tools/Binn/sqldmo.dll
用.Net框架提供的工具转成受托管代码
tlbimp sqldmo.dll /out: c:/SQLDMO.dll
SQLDMO由Microsoft SQL Server自带的SQLDMO.dll提供，由于SQLDMO.dll是一个COM对象，所以大家在用之前必须在.NET项目中添加对它的引用。

利用SQLDMO实现的，只要添加SQLDMO引用就好了，然后利用下边的类的方法就可以实现了。
我把原作者的类扩充了一下，可以自动识别web.config里的数据库连接字符串，可以通过变量设置还原恢复的信息。
需要注意的是还原，还原的时候问题最大了，有别的用户使用数据库的时候无法还原，解决办法就是在MASTER数据库中添加一个存储过程：
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status<>-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
GO
在还原之前先执行这个存储过程，需要传递dbname，就是你的数据库的名字。下边是类的原代码：（web.config里的数据库连接字符串是constr）
******************************************************************************************************************/
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace web.base_class
{
      /**//// <summary>
      /// DbOper类,主要应用SQLDMO实现对Microsoft SQL Server数据库的备份和恢复
      /// </summary>
      public class DbOper
      {
           private string server;
           private string uid;
           private string pwd;
           private string database;
           private string conn;
          /**//// <summary>
          /// DbOper类的构造函数
          /// </summary>
          public DbOper()
          {
               conn=System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
               server=cut(conn,"server=",";");
               uid=cut(conn,"uid=",";");
               pwd=cut(conn,"pwd=",";");
               database=cut(conn,"database=",";");
          }
          public string cut(string str,string bg,string ed)
          {
               string sub;
               sub=str.Substring(str.IndexOf(bg)+bg.Length);
               sub=sub.Substring(0,sub.IndexOf(";"));
               return sub;
          }
          /**//// <summary>
          /// 数据库备份
          /// </summary>
          public   bool DbBackup(string url)

          {
               SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
               SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
               try
               {
                    oSQLServer.LoginSecure = false;
                    oSQLServer.Connect(server,uid, pwd);
                    oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                    oBackup.Database = database;
                    oBackup.Files = url;//"d:/Northwind.bak";
                    oBackup.BackupSetName = database;
                    oBackup.BackupSetDescription = "数据库备份";
                    oBackup.Initialize = true;
                    oBackup.SQLBackup(oSQLServer);
                    return true;
               }
               catch
               {
                    return false;
                    throw;
               }
               finally
               {
                    oSQLServer.DisConnect();
               }
          }
          /**//// <summary>
          /// 数据库恢复
          /// </summary>
          public string DbRestore(string url)
          {
               if(exepro()!=true)//执行存储过程
               {
                    return "操作失败";
               }
               else
               {
                    SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
                    SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
                    try
                    {
                         oSQLServer.LoginSecure = false;
                         oSQLServer.Connect(server, uid, pwd);
                         oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                         oRestore.Database = database;
                         oRestore.Files = url;//@"d:/Northwind.bak";
                         oRestore.FileNumber = 1;
                         oRestore.ReplaceDatabase = true;
                         oRestore.SQLRestore(oSQLServer);
                        return "ok";
                    }
                    catch(Exception e)
                    {
                        return "恢复数据库失败";
                        throw;
                    }
                    finally
                    {
                         oSQLServer.DisConnect();
                    }
               }
          }

private bool exepro()

          {
               SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
               SqlCommand cmd = new SqlCommand("killspid",conn1);
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.Add("@dbname","port");
               try

               {
                    conn1.Open();
                    cmd.ExecuteNonQuery();
                    return true;
               }
               catch(Exception ex)
               {
                    return false;
               }
               finally

               {
                    conn1.Close();
               }
          }
      }
}

//数据库备份
        string backaway =textbox1.Text.Trim();
            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                //下面设置登录sql服务器的ip,登录名,登录密码
                oSQLServer.Connect(serverip, serverid, serverpwd);
                oBackup.Action = 0;
               //下面两句是显示进度条的状态
                SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step2);
                oBackup.PercentComplete += pceh;
                //数据库名称:
                oBackup.Database = "k2";
                //备份的路径
                oBackup.Files = @backaway;
                //备份的文件名
                oBackup.BackupSetName = "k2";
                oBackup.BackupSetDescription = "数据库备份";
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
                MessageBox.Show("备份成功！", "提示");
            }
            catch
            {
                MessageBox.Show("备份失败！", "提示");
            }
            finally
            {
                oSQLServer.DisConnect();
            }

//数据库恢复
        //获取恢复的路径
        string dbaway = textbox2.Text.Trim();
            SQLDMO.Restore restore = new SQLDMO.RestoreClass();
            SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
            server.Connect(serverip, serverid, serverpwd);

            //KILL DataBase Process
            conn = new 工资管理系统.CCUtility.connstring();
            conn.DBOpen();
            SqlCommand cmd = new SqlCommand("use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='k2'", conn.Connection);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                server.KillProcess(Convert.ToInt32(dr[0].ToString()));
            }
            dr.Close();
            conn.DBClose();

            try
            {
                restore.Action = 0;
                SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
                restore.PercentComplete += pceh;
                restore.Database = "k2";
                restore.Files = @dbaway;
                restore.ReplaceDatabase = true;
                restore.SQLRestore(server);
                MessageBox.Show("数据库恢复成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                server.DisConnect();
            }