数据库的备份与还原

最新推荐文章于 2022-07-30 16:16:34 发布
最新推荐文章于 2022-07-30 16:16:34 发布
阅读量671 收藏
点赞数
分类专栏： asp.net
是用SMO备份的  记着添加SMO相关的dll引用  备份按钮里调备份函数      还原按钮调还原函数就行了 
备份函数  
private void RestoreBase()
        {
            ServerConnection sConn = new ServerConnection("sqlserver所在机器的Ip", "用户名", “密码”);
            Server s=new Server(sConn);
            s.ConnectionContext.Connect();
            Restore restore=new Restore();
            restore.Database = "数据库名";
            restore.ReplaceDatabase = true;
            restore.Action = RestoreActionType.Database;
           //D:\testFullBackUp.bak  备份路径   D盘是SQlserve所在的机器D盘
            restore.Devices.Add(new BackupDeviceItem(@"D:\testFullBackUp.bak", DeviceType.File));
            restore.SqlRestore(s);

        }
还原函数：
private void RestoreBase()
        {
            ServerConnection sConn = new ServerConnection("sqlserver所在机器的Ip", "用户名", “密码”);
            Server s=new Server(sConn);
            s.ConnectionContext.Connect();
            Restore restore=new Restore();
            restore.Database = "数据库名";
            restore.ReplaceDatabase = true;
            restore.Action = RestoreActionType.Database;
         //D:\testFullBackUp.bak    D盘是SQlserve所在的机器D盘
            restore.Devices.Add(new BackupDeviceItem(@"D:\testFullBackUp.bak", DeviceType.File));
            restore.SqlRestore(s);

        }




ASP.NET数据库备份和还原
<%@ page import="org.jfree.chart.JFreeChart"%>
<%@ page import="org.jfree.chart.ChartFactory"%>先导入Interop.SQLDMO.dll
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// DBOperater 的摘要说明
/// </summary>
public class DBOperater
{
public DBOperater()
{
  //
  // TODO: 在此处添加构造函数逻辑
  //
}
    //数据库备份
    public static string DbBackup(string dbName,string backupDBName)
    {
         SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
         SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
         try
         {
           
             Object aip = ip;
             oSQLServer.LoginSecure = false;
             oSQLServer.Connect("127.0.0.1","sa","sa");
             oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
             oBackup.Database = dbName;
             oBackup.Files = @"d:\\aaa\\"+backupDBName+".bak";
             oBackup.BackupSetName = backupDBName;
             oBackup.BackupSetDescription = "数据库备份";
             oBackup.Initialize = true;
             oBackup.SQLBackup(oSQLServer);
             return "数据库已成功经备份到["+oBackup.Files+"]";
        }
        catch(Exception ex)
        {
            throw new Exception("数据库备份失败: "+ex.Message);
        }
        finally
        {
            oSQLServer.DisConnect();
        }
    }
  /// 
  /// 数据库恢复
  /// 
    public static string  DbRestore(string dbName,string backupFile)
    {
        SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
        SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
        try
        {
            oSQLServer.LoginSecure = false;
            oSQLServer.Connect("192.168.1.110", "new", "");
            oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
            oRestore.Database = dbName;
            oRestore.Files = @"d:\\aaa\\"+backupFile;
            oRestore.FileNumber = 1;
            oRestore.ReplaceDatabase = true;
            oRestore.SQLRestore(oSQLServer);
            return "数据库"+dbName+"已经成功恢复!";
        }
        catch(Exception ex)
        {
            throw new Exception("数据库恢复失败: "+ex.Message );
        }
        finally
        {
            oSQLServer.DisConnect();
        }
    }
}


 
 
存储过程
 CREATE  PROCEDURE sp_KillThread  @dbname  varchar(20) as begin declare  @sql  nvarchar(500),@temp varchar(1000) declare  @spid  int set  @sql='declare  getspid  cursor  for select  spid  from  master..sysprocesses  where  dbid=db_id('''+@dbname+''')'   exec  (@sql)  
 open  getspid   fetch  next  from  getspid  into  @spid while  @@fetch_status <> -1 begin   set @temp='kill  '+rtrim(@spid)   exec(@temp) fetch  next  from  getspid  into  @spid end close  getspid deallocate  getspid end GO
页面
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    //备份
    protected void Button1_Click(object sender, EventArgs e)
    {
        
            try
            {
                //string path = this.File1.Value;//备份到...
                string ret = DBOperater.DbBackup(t_db.Text, t_fileName.Text);
                this.Label1.Text = ret;
            }
            catch (Exception ex)
            {
                this.Label1.Text = ex.Message;
            }
        
    }
    //恢复
    protected void Button2_Click(object sender, EventArgs e)
    {
        string path = "d:\\aaa\\";//this.File1.Value;//恢复文件路径
        //杀死所有访问该数据库的进程
        string conStr = "data source=localhost;database=master;user id=sa;password=password";
        SqlConnection con = new SqlConnection(conStr);
        string cmdText = String.Format("EXEC sp_KillThread @dbname='{0}'", t_db.Text);
        SqlCommand com = new SqlCommand(cmdText, con);
        try
        {
            con.Open();
            com.ExecuteNonQuery();
            con.Close();
            //恢复数据库
            string ret = DBOperater.DbRestore(t_db.Text, path);
            this.Label1.Text = ret;
        }
        catch (Exception ex)
        {
            con.Close();
            this.Label1.Text = ex.Message;
        }
    }
}


/// <summary>
 2 /// 通过调用MSSQL的SQLDMO.DLL文件来实现备份数据库
 3 /// 1.首先在在项目中引用SQLDMO.DLL文件。
 4 /// 2.在引用中的SQLDMO.DLL文件右击-->属性-->设置[嵌入互操作类型]为flash
 5 /// </summary>
 6 public static void DBBackup()
 7 {
 8     SQLDMO.Backup dbBackup = new SQLDMO.BackupClass();
 9     SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServerClass();
10     try
11     {
12         sqlServer.LoginSecure = false;
13         sqlServer.Connect("localhost", "sa", "");
14         dbBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
15         dbBackup.Database = "kuang";    //数据库名字
16         dbBackup.Files = @"d:\kuang_bak.bak";   //备份位置
17         dbBackup.BackupSetName = "kuang";   //名称,在企业管理器里面选择备份的时候也有这个，貌似没用。
18         dbBackup.BackupSetDescription = "数据库备份"; //描述,在企业管理器里面选择备份的时候也有这个，貌似没用。
19         dbBackup.Initialize = true;
20         dbBackup.SQLBackup(sqlServer);
21     }
22     catch
23     {
24         throw;
25     }
26     finally
27     {
28         sqlServer.DisConnect();
29     }
30 }

 
 

  
  复制代码
 
 
 

 
 

  
  

   
   复制代码
  
  

  
  
 1 /// <summary>
 2 /// 通过调用MSSQL的SQLDMO.DLL文件来实现从备份文件恢复到是数据库
 3 /// 注：恢复是数据库必须是没有链接的。清楚链接的方法：在企业管理器右击数据库-->分离数据库-->点击清楚按钮即可
 4 /// </summary>
 5 private void DBReply()
 6 {
 7     SQLDMO.Restore restore = new SQLDMO.RestoreClass();
 8     SQLDMO.SQLServer sqlserver = new SQLDMO.SQLServerClass();
 9     sqlserver.LoginSecure = false;
10     sqlserver.Connect("localhost", "sa", "");
11     restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
12     restore.Database = "kuang";
13     restore.Files = @"d:\kuang_bak.bak";
14     restore.FileNumber = 1;
15     restore.SQLRestore(sqlserver);
16 }

http://blog.csdn.net/x276912755/article/details/6087221

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
SqlCommandcmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHEREsysprocesses.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();
SqlCommandcmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHEREsysprocesses.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; 

测试通过的
proc
 use master  
    go  
      
    if object_id('killspid','P') is not null  
    drop proc killspid  
    go  
    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  

 public bool killThread(string dbName)
        {
            SqlConnection conn= new SqlConnection("server=.;uid=sa;pwd=sa;database=master");
            SqlCommand cmd = new SqlCommand("killspid", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@dbname", dbName);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }  

  /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="filepath"></param>
        public static string DBBackup(string filepath,string dbName,string backupDBName)
        {

            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(".", "sa", "sa");
                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                oBackup.Database = dbName;
                oBackup.Files = @"d:\" + backupDBName + ".bak";
                oBackup.BackupSetName = backupDBName;
                oBackup.BackupSetDescription = "数据库备份";
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
                return "数据库已经成功备份到服务器[" + oBackup.Files + "]";
            }
            catch (Exception ex)
            {
                throw new Exception("数据库备份失败: " + ex.Message);
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="filepath"></param>
        public static string DBReply(string filepath,string dbName,string backupFile)
        {
            SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(".", "sa", "sa");
                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                oRestore.Database = dbName;
                oRestore.Files = @"d:\" + backupFile + ".bak";
                oRestore.FileNumber = 1;
                oRestore.ReplaceDatabase = true;
                oRestore.SQLRestore(oSQLServer);
                return "数据库" + dbName + "已经成功恢复!";
            }
            catch (Exception ex)
            {
                throw new Exception("数据库恢复失败: " + ex.Message);
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }



 
 
 

金色之星
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库的备份与恢复
weixin_30793643的博客
03-11 63
1.首先在项目中加入 SQL Server 管理对象 Interop.SQLDMO.dll 的引用(它是一个COM组件,名称是Microsoft SQLDMO Object Library) 2.创建一个类对它操作,实现两个方法(备份和恢复) using System; namespace maxclient{?/// ?/// 数据库备份和恢复?/// ?public class DBOp...
SQL Server数据库备份与还原
IndependentRed的博客
04-09 1089
SQL Server 备份 还原
DM数据库备份与还原
m0_72514662的博客
07-30 2142
本文介绍了DM数据库备份与还原的知识原理及还原方法，并进行了实操练习
数据库备份与还原
10-13
数据库备份与还原是IT领域中一个至关重要的环节，特别是在企业级应用中，数据的安全性和完整性是业务持续运行的基础。在数据库系统遭受意外故障、硬件损坏、软件错误或恶意攻击时，能够快速恢复到正常状态至关重要。...
MySql数据库备份与还原！！！
04-10
数据库备份的分类和备份策略 数据库备份的分类 1）物理备份 对数据库操作系统的物理文件（如数据文件，日志文件等）的备份，物理备份的方法： 1.冷备份（脱机备份）：是在关闭数据库的时候进行的。 2.热备份（联机...
JAVA实现数据库备份与还原.pdf
11-24
JAVA实现数据库备份与还原.pdf
Oracle数据库备份与还原命令.pdf
11-16
Oracle数据库备份与还原命令.pdf
红色龙年2024新年工作计划PPT模板
07-13
【作品名称】：红色龙年2024新年工作计划PPT模板 【适用人群】：适用于希望学习不同技术领域的小白或进阶学习者。可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。
项目管理策划编写模板.docx
07-13
项目管理策划编写模板.docx
架子管及扣件现场管理规定.docx
07-13
架子管及扣件现场管理规定.docx
Swift语言教程&案例.docx
07-13
Swift 是由 Apple 开发的一种现代、安全、快速的编程语言，适用于 iOS、macOS、watchOS 和 tvOS 应用程序开发。以下是一个详细的 Swift 语言教程，适合初学者逐步学习。
ctf_tools_list.md
07-13
ctf_tools_list
巴比达内网穿透.txt
07-13
巴比达内网穿透
URL编码.exe
07-13
URL编码
scipy-1.10.0rc1-cp39-cp39-manylinux-2-17-x86-64.manylinux2014-x8
最新发布
07-13
scipy-1.10.0rc1-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl
红色灰色商务年终总结汇报ppt模板
07-13
【作品名称】：红色灰色商务年终总结汇报ppt模板 【适用人群】：适用于希望学习不同技术领域的小白或进阶学习者。可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。
数据库还原与备份教程
05-28
数据库备份与还原是数据库知识中非常重要的一部分。作为数据库管理员（DBA），备份和还原数据库是我们最重要的任务之一。通过备份数据库，我们可以将数据库中的数据和结构保存下来，以便在发生灾难时能够恢复数据库...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值