代码改变世界

[转载]如何用SQLDMO在ASP.NET页面下实现数据库的备份与恢复

2008-08-26 13:53  Virus-BeautyCode  阅读(812)  评论(1编辑  收藏  举报

本文转载自:http://www.cnblogs.com/terrylee/articles/253667.html

原文作者:
  • 网名:TerryLee
  • 本名:李会军
  • 位置:中国 北京
我们知道,用SQLDMO可以实现对数据库的备份与恢复,下面给出简单的实现方法。
首先需要添加对SQLDMO引用

1.实现数据库的备份:
 1/// <summary>
 2        /// 数据库备份
 3        /// </summary>
 4        /// <returns>备份是否成功</returns>

 5        public bool DbBackup()
 6        {
 7            string path = CreatePath();
 8            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
 9            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
10            try
11            {
12                oSQLServer.LoginSecure = false;
13                oSQLServer.Connect(server,uid, pwd);
14                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
15                oBackup.Database = database;
16                oBackup.Files = path;
17                oBackup.BackupSetName = database;
18                oBackup.BackupSetDescription = "数据库备份";
19                oBackup.Initialize = true;
20                oBackup.SQLBackup(oSQLServer);
21
22                return true;
23            }

24            catch(Exception ex)
25            {
26                return false;
27                throw ex;
28            }

29            finally
30            {
31                oSQLServer.DisConnect();
32            }

33        }

2.实现数据库恢复:
在恢复时要注意先杀掉当前数据库的所有进程
 1/// <summary>
 2        /// 数据库恢复
 3        /// </summary>

 4        public string DbRestore()
 5        {
 6            if(exepro()!=true)//执行存储过程
 7            {
 8                return "操作失败";
 9            }

10            else
11            {
12                SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
13                SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
14                try
15                {
16                    exepro();
17                    oSQLServer.LoginSecure = false;
18                    oSQLServer.Connect(server, uid, pwd);
19                    oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
20                    oRestore.Database = database;
21                    ///自行修改
22                    oRestore.Files = @"d:\aaa\aaa.bak";
23                    oRestore.FileNumber = 1;
24                    oRestore.ReplaceDatabase = true;
25                    oRestore.SQLRestore(oSQLServer);
26
27                    return "ok";
28                }

29                catch(Exception e)
30                {
31                    return "恢复数据库失败";
32                    throw e;
33                }

34                finally
35                {
36                    oSQLServer.DisConnect();
37                }

38            }

39        }

40        
41        /// <summary>
42        /// 杀死当前库的所有进程
43        /// </summary>
44        /// <returns></returns>

45        private bool exepro()
46        {
47
48            SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
49            SqlCommand cmd = new SqlCommand("killspid",conn1);
50            cmd.CommandType = CommandType.StoredProcedure;
51            cmd.Parameters.Add("@dbname","aaa");
52            try
53            {
54                conn1.Open();
55                cmd.ExecuteNonQuery();
56                return true;
57            }

58            catch(Exception ex)
59            {
60                return false;
61            }

62            finally
63            {
64                conn1.Close();
65            }

66        }

完整的操作类如下:
  1using System;
  2using System.Collections;
  3using System.Data;
  4using System.Data.SqlClient;
  5
  6namespace DbBackUp
  7{
  8    /// <summary>
  9    /// 创建人:Terrylee
 10    /// 创建时间:2005年8月1日
 11    /// 功能描述:实现数据库的备份和还原
 12    /// 更新记录:
 13    /// </summary>

 14    public class DbOperate
 15    {    
 16        /// <summary>
 17        /// 服务器
 18        /// </summary>

 19        private string server;
 20        
 21        /// <summary>
 22        /// 登录名
 23        /// </summary>

 24        private string uid;
 25        
 26        /// <summary>
 27        /// 登录密码
 28        /// </summary>

 29        private string pwd;
 30        
 31        /// <summary>
 32        /// 要操作的数据库
 33        /// </summary>

 34        private string database;
 35        
 36        /// <summary>
 37        /// 数据库连接字符串
 38        /// </summary>

 39        private string conn;
 40
 41        /// <summary>
 42        /// DbOperate类的构造函数
 43        /// 在这里进行字符串的切割,获取服务器,登录名,密码,数据库
 44        /// </summary>

 45        public DbOperate()
 46        {
 47            conn = System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
 48            server = StringCut(conn,"server=",";");
 49            uid = StringCut(conn,"uid=",";");
 50            pwd = StringCut(conn,"pwd=",";");
 51            database = StringCut(conn,"database=",";");
 52        }

 53        
 54        /// <summary>
 55        /// 切割字符串
 56        /// </summary>
 57        /// <param name="str"></param>
 58        /// <param name="bg"></param>
 59        /// <param name="ed"></param>
 60        /// <returns></returns>

 61        public string StringCut(string str,string bg,string ed)
 62        {
 63            string sub;
 64            sub=str.Substring(str.IndexOf(bg)+bg.Length);
 65            sub=sub.Substring(0,sub.IndexOf(";"));
 66            return sub;
 67        }

 68        
 69        /// <summary>
 70        /// 构造文件名
 71        /// </summary>
 72        /// <returns>文件名</returns>

 73        private string CreatePath()
 74        {
 75            string CurrTime = System.DateTime.Now.ToString();
 76            CurrTime = CurrTime.Replace("-","");
 77            CurrTime = CurrTime.Replace(":","");
 78            CurrTime = CurrTime.Replace(" ","");
 79            CurrTime = CurrTime.Substring(0,12);
 80            string path = @"d:\\aaa\\";
 81            path += database;
 82            path += "_db_";
 83            path += CurrTime;
 84            path += ".BAK";
 85            return path;
 86        }

 87
 88        /// <summary>
 89        /// 数据库备份
 90        /// </summary>
 91        /// <returns>备份是否成功</returns>

 92        public bool DbBackup()
 93        {
 94            string path = CreatePath();
 95            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
 96            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
 97            try
 98            {
 99                oSQLServer.LoginSecure = false;
100                oSQLServer.Connect(server,uid, pwd);
101                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
102                oBackup.Database = database;
103                oBackup.Files = path;
104                oBackup.BackupSetName = database;
105                oBackup.BackupSetDescription = "数据库备份";
106                oBackup.Initialize = true;
107                oBackup.SQLBackup(oSQLServer);
108
109                return true;
110            }

111            catch(Exception ex)
112            {
113                return false;
114                throw ex;
115            }

116            finally
117            {
118                oSQLServer.DisConnect();
119            }

120        }

121
122        /// <summary>
123        /// 数据库恢复
124        /// </summary>

125        public string DbRestore()
126        {
127            if(exepro()!=true)//执行存储过程
128            {
129                return "操作失败";
130            }

131            else
132            {
133                SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
134                SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
135                try
136                {
137                    exepro();
138                    oSQLServer.LoginSecure = false;
139                    oSQLServer.Connect(server, uid, pwd);
140                    oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
141                    oRestore.Database = database;
142                    ///自行修改
143                    oRestore.Files = @"d:\aaa\aaa.bak";
144                    oRestore.FileNumber = 1;
145                    oRestore.ReplaceDatabase = true;
146                    oRestore.SQLRestore(oSQLServer);
147
148                    return "ok";
149                }

150                catch(Exception e)
151                {
152                    return "恢复数据库失败";
153                    throw e;
154                }

155                finally
156                {
157                    oSQLServer.DisConnect();
158                }

159            }

160        }

161        
162        /// <summary>
163        /// 杀死当前库的所有进程
164        /// </summary>
165        /// <returns></returns>

166        private bool exepro()
167        {
168
169            SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
170            SqlCommand cmd = new SqlCommand("killspid",conn1);
171            cmd.CommandType = CommandType.StoredProcedure;
172            cmd.Parameters.Add("@dbname","aaa");
173            try
174            {
175                conn1.Open();
176                cmd.ExecuteNonQuery();
177                return true;
178            }

179            catch(Exception ex)
180            {
181                return false;
182            }

183            finally
184            {
185                conn1.Close();
186            }

187        }

188
189    }

190
191}

192

在相应的按钮
1<asp:Button id="wbtn_Backup" runat="server" Width="60px" Text="备 份" CssClass="Button"></asp:Button>
单击事件里调用即可:
 1/// <summary>
 2        /// 备份按钮
 3        /// </summary>
 4        /// <param name="sender"></param>
 5        /// <param name="e"></param>

 6        private void wbtn_Backup_Click(object sender, System.EventArgs e)
 7        {
 8            DbOperate dbop = new DbOperate();
 9            dbop.DbBackup();
10        }
^_^
作者:TerryLee
出处:http://terrylee.cnblogs.com
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

个人对上面的版本做了一些小的修整,谢谢李会军作出的共享.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace WindowsApp
{
    
/// <summary>
    
/// 功能描述:实现SQL Server 2005数据库的备份和恢复
    
/// </summary>
    public class DBOperate
    {
        
/// <summary>
        
/// 服务器
        
/// </summary>
        private string _server;
        
/// <summary>
        
/// 服务器
        
/// </summary>
        public string Server
        {
            
get { return _server; }
            
set { _server = value; }
        }
        
/// <summary>
        
/// 登录名
        
/// </summary>
        private string _uid;
        
/// <summary>
        
/// 登录名
        
/// </summary>
        public string Uid
        {
            
get { return _uid; }
            
set { _uid = value; }
        }
        
/// <summary>
        
/// 登录密码
        
/// </summary>
        private string _pwd;
        
/// <summary>
        
/// 登录密码
        
/// </summary>
        public string Pwd
        {
            
get { return _pwd; }
            
set { _pwd = value; }
        }
        
/// <summary>
        
/// 数据库
        
/// </summary>
        private string _database;
        
/// <summary>
        
/// 数据库
        
/// </summary>
        public string Database
        {
            
get { return _database; }
            
set { _database = value; }
        }
        
/// <summary>
        
/// 连接字符串
        
/// </summary>
        private string _conn;
        
/// <summary>
        
/// 连接字符串
        
/// </summary>
        public string Conn
        {
            
get { return _conn; }
            
set { _conn = value; }
        }
        
/// <summary>
        
/// 默认构造函数
        
/// </summary>
        public DBOperate()
        {

        }
        
/// <summary>
        
/// 构造函数
        
/// </summary>
        
/// <param name="database">数据库</param>
        
/// <param name="uid">登录名</param>
        
/// <param name="pwd">登录密码</param>
        
/// <param name="server">服务器</param>
        public DBOperate(string database,string uid,string pwd,string server)
        {
            
this._server = server;
            
this._uid = uid;
            
this._pwd = pwd;
            
this._database = database;
        }
        
/// <summary>
        
///     切割字符串
        
/// </summary>
        
/// <param name="str"></param>
        
/// <param name="bg"></param>
        
/// <param name="ed"></param>
        
/// <returns></returns>
        public string StringCut(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>
        
/// <returns>文件名</returns>
        private string CreatePath()
        {
            
string currTime = System.DateTime.Now.ToString();
            currTime 
= currTime.Replace("-""");
            currTime 
= currTime.Replace(":""");
            currTime 
= currTime.Replace(" """);
            currTime 
= currTime.Substring(012);
            
string path = @"d:\\aaa\\";
            path 
+= _database;
            path 
+= "_db_";
            path 
+= currTime;
            path 
+= ".BAK";
            
return path;
        }
        
/// <summary>
        
/// 数据库备份
        
/// </summary>
        
/// <param name="file">备份文件的完整存放路径,包括文件名</param>
        
/// <returns>备份是否成功</returns>
        public bool DBBackup(string file)
        {
            
//string path = CreatePath();
            SQLDMO.Backup backup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer sqlserver 
= new SQLDMO.SQLServerClass();
            
try
            {
                sqlserver.LoginSecure 
= false;
                sqlserver.Connect(_server, _uid, _pwd);
                backup.Action 
= SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                backup.Database 
= _database;
                backup.Files 
= file;
                backup.BackupSetName 
= _database;
                backup.BackupSetDescription 
= "数据库备份";
                backup.Initialize 
= true;
                backup.SQLBackup(sqlserver);

                
return true;
            }
            
catch (Exception ex)
            {
               
                
throw ex;
            }
            
finally
            {
                sqlserver.DisConnect();
            }
        }
        
/// <summary>
        
///     数据库恢复
        
/// </summary>
        
/// <param name="file">备份文件的完整存放路径,包括文件名</param>
        
/// <returns></returns>
        public string DBRestore(string file)
        {
           
                SQLDMO.Restore restore 
= new SQLDMO.RestoreClass();
                SQLDMO.SQLServer sqlserver 
= new SQLDMO.SQLServerClass();
                
try
                {
                    killProcess(_database);
                    sqlserver.LoginSecure 
= false;
                    sqlserver.Connect(_server, _uid, _pwd);
                    restore.Action 
= SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                    restore.Database 
= _database;
                    
//自行修改
                    restore.Files = file;
                    restore.FileNumber 
= 1;

                    restore.ReplaceDatabase 
= true;
                    restore.SQLRestore(sqlserver);

                    
return "ok";
                }
                
catch (Exception ex)
                {
                   
                    
throw ex;
                }
                
finally
                {
                    sqlserver.DisConnect();
                }
            
        }
        
/// <summary>
        
/// 杀死当前库的所有进程
        
/// </summary>
        
/// <param name="dbName">数据库名称</param>
        
/// <returns></returns>
        private bool killProcess(string dbName)
        {
            SqlConnection conn 
= new SqlConnection("server=" + _server + ";uid=" + _uid + ";pwd=" + _pwd + ";database=master");
            SqlCommand comm 
= new SqlCommand("killspid", conn);
            comm.CommandType 
= CommandType.StoredProcedure;
            comm.Parameters.AddWithValue(
"@dbname", dbName);
            
try
            {
                conn.Open();
                comm.ExecuteNonQuery();
                
return true;
            }
            
catch (Exception ex)
            {
                
throw ex;
            }
            
finally
            {
                conn.Close();
            }
        }
    }

}


使用范例如下:

private void backup_Click(object sender, EventArgs e)
        {
            DBOperate dbOperate 
= new DBOperate("DevTest""sa""123", "sql2005");
            
try
            {

                
if (dbOperate.DBBackup(@"d:\123.bak"))
                {
                    MessageBox.Show(
"备份成功""提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            
catch (Exception ex)
            {
                MessageBox.Show(
"数据库备份失败,失败原因:" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        
private void restore_Click(object sender, EventArgs e)
        {
           
            DBOperate dbOperate 
= new DBOperate("DevTest""sa""123"@"sql2005");
            
try
            {
                
if (dbOperate.DBRestore(@"d:\123.bak").Equals("ok"))
                {
                    MessageBox.Show(
"恢复成功""提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            
catch (Exception ex)
            {
                MessageBox.Show(
"数据库恢复失败,失败原因:" + ex.Message,"提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }



程序中使用的存储过程killspid如下:
use master
go
---------------------------------------
--
关闭指定数据库的全部访问进程
--
-------------------------------------------
create proc killspid
@dbname varchar(200--要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go