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

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

原文作者：

网名：TerryLee
本名：李会军
位置：中国北京

我们知道，用SQLDMO可以实现对数据库的备份与恢复，下面给出简单的实现方法。
首先需要添加对SQLDMO引用

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

return true;
23

}
24

catch(Exception ex)
25

{
26

return false;
27

throw ex;
28

}
29

finally
30

{
31

oSQLServer.DisConnect();
32

}
33

}

2.实现数据库恢复：
在恢复时要注意先杀掉当前数据库的所有进程

/// <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

return "ok";
28

}
29

catch(Exception e)
30

{
31

return "恢复数据库失败";
32

throw e;
33

}
34

finally
35

{
36

oSQLServer.DisConnect();
37

}
38

}
39

}
40

/// <summary>
42

/// 杀死当前库的所有进程
43

/// </summary>
44

/// <returns></returns>
45

private bool exepro()
46

{
47

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

}

完整的操作类如下：

using System;
2

using System.Collections;
3

using System.Data;
4

using System.Data.SqlClient;
5

namespace 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

/// <summary>
22

/// 登录名
23

/// </summary>
24

private string uid;
25

/// <summary>
27

/// 登录密码
28

/// </summary>
29

private string pwd;
30

/// <summary>
32

/// 要操作的数据库
33

/// </summary>
34

private string database;
35

/// <summary>
37

/// 数据库连接字符串
38

/// </summary>
39

private string conn;
40

/// <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

/// <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

/// <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

/// <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

在相应的按钮

<asp:Button id="wbtn_Backup" runat="server" Width="60px" Text="备份" CssClass="Button"></asp:Button>

单击事件里调用即可：

/// <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(0, 12);
            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

【Blog】http://virusswb.cnblogs.com/

【MSN】jorden008@hotmail.com

【说明】转载请标明出处，谢谢

反馈文章质量，你可以通过快速通道评论：