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

关注

转载最新推荐文章于 2026-09-23 10:25:38 发布 1k 阅读 AI 写同款· GEO 优化 ›

【智能制造】- APS系列｜05 APS 排产的五大底层算法逻辑很多人误以为APS只是给生产工单排先后顺序，实则整套系统依托成熟算法体系，完成多维度约束下的最优生产决策在制造业数字化排产普及的当下，依旧有不少制造管理者对APS存在认知误区，将其等同于简易工单排序工具。事实上，专业级高级计划与排程系统，早已脱离单纯按时间排序的基础功能，而是依托一整套成熟完善的底层算法逻辑，整合现场各类生产限制条件，在海量变量中自动推演、筛选、输出最贴合企业经营目标的生产方案。阅读详情

如何用SQLDMO在ASP.NET页面下实现数据库的备份与恢复 [ 日期：2005-10-27 ] [ 来自：博客园 ]

我们知道，用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        }

程序代码：

[ 复制代码到剪贴板 ]

CREATE PROCEDURE killspid
/*----------------------------------
创建人：Terrylee
创建事件：2005年8月1日
功能说明：
参数说明：
----------------------------------*/
(
@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+''')'
EXECUTE (@sql)
OPEN getspid
FETCH NEXT FROM getspid INTO @spid
WHILE @@fetch_status<>-1
  BEGIN
   EXECUTE('kill '+@spid)
   FETCH NEXT FROM getspid INTO @spid
  END
CLOSE getspid
DEALLOCATE getspid
END
GO

原文地址：http://terrylee.cnblogs.com/archive/2005/10/13/253667.html

收录于