csharp: SQL Server 2005 Database Backup and Restore using C#

1.第一种方式: using SQLDMO;//Microsoft SQLDMO Object Library 8.0

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
/// <summary>
       /// 数据库的备份
       /// 涂聚文注:数据库的备份和实时进度显示代码：(远程备份在数据库原本地,如果在数据库安装的电脑上备份,就可以自行选择文件夹地址,不能备份在客户端的电脑上)
       /// 20150205
       /// 默认: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup (我装了2000,20005)
       /// </summary>
       /// <param name="ServerName"></param>
       /// <param name="UserName"></param>
       /// <param name="Password"></param>
       /// <param name="strDbName"></param>
       /// <param name="strFileName"></param>
       /// <param name="pgbMain"></param>
       /// <returns></returns>
       public bool BackUPDB(string ServerName, string UserName, string Password, string strDbName, string strFileName, ProgressBar pgbMain)
       {
 
           PBar = pgbMain;
           SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
           try
           {
               svr.Connect(ServerName, UserName, Password);
               SQLDMO.Backup bak = new SQLDMO.BackupClass();
               bak.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;// 0;               
               SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
               bak.PercentComplete += pceh;
               bak.BackupSetDescription = "数据库备份";
               bak.Files = strFileName;
               bak.Database = strDbName;
               bak.Initialize = true;
               bak.SQLBackup(svr);
 
               return true;
           }
           catch (Exception err)
           {
               throw (new Exception("备份数据库失败" + err.Message));
               //return false ;
               //MessageBox.Show("备份数据库失败"+err.Message);
           }
           finally
           {
               svr.DisConnect();
           }
       }
 
       /// <summary>
       /// 数据库的恢复的代码：
       /// </summary>
       /// <param name="ServerName"></param>
       /// <param name="UserName"></param>
       /// <param name="Password"></param>
       /// <param name="strDbName"></param>
       /// <param name="strFileName"></param>
       /// <param name="pgbMain"></param>
       /// <returns></returns>
       public bool RestoreDB(string ServerName, string UserName, string Password, string strDbName, string strFileName, ProgressBar pgbMain)
       {
           PBar = pgbMain;
           SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
           try
           {
               svr.Connect(ServerName, UserName, Password);
               SQLDMO.QueryResults qr = svr.EnumProcesses(-1);
               int iColPIDNum = -1;
               int iColDbName = -1;
               for (int i = 1; i <= qr.Columns; i++)
               {
                   string strName = qr.get_ColumnName(i);
                   if (strName.ToUpper().Trim() == "SPID")
                   {
                       iColPIDNum = i;
                   }
                   else if (strName.ToUpper().Trim() == "DBNAME")
                   {
                       iColDbName = i;
                   }
                   if (iColPIDNum != -1 && iColDbName != -1)
                       break;
               }
 
               for (int i = 1; i <= qr.Rows; i++)
               {
                   int lPID = qr.GetColumnLong(i, iColPIDNum);
                   string strDBName = qr.GetColumnString(i, iColDbName);
                   if (strDBName.ToUpper() == strDbName.ToUpper())
                       svr.KillProcess(lPID);
               }
 
 
               SQLDMO.Restore res = new SQLDMO.RestoreClass();
               res.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database; //0;
               SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
               res.PercentComplete += pceh;
               res.Files = strFileName;
 
               res.Database = strDbName;
               res.ReplaceDatabase = true;
               res.SQLRestore(svr);
               return true;
           }
           catch (Exception err)
           {
               throw (new Exception("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message));
               //return false ;
               //MessageBox.Show("恢复数据库失败,请关闭所有和该数据库连接的程序！"+err.Message);
           }
           finally
           {
               svr.DisConnect();
           }
       }

 2.第二种方式:

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
/// <summary>
    /// https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx
    /// https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx
    /// </summary>
    public class RestoreHelper
    {
        /// <summary>
        ///
        /// </summary>
        public RestoreHelper()
        {
 
        }
        /// <summary>
        /// 还原数据库
        /// 涂聚文
        /// </summary>
        /// <param name="databaseName"></param>
        /// <param name="filePath"></param>
        /// <param name="serverName"></param>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <param name="dataFilePath"></param>
        /// <param name="logFilePath"></param>
        public void RestoreDatabase(String databaseName, String filePath, String serverName, String userName, String password, String dataFilePath, String logFilePath)
        {
            try
            {
                Restore sqlRestore = new Restore();
 
                BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
                sqlRestore.Devices.Add(deviceItem);
                sqlRestore.Database = databaseName;
 
                ServerConnection connection = new ServerConnection(serverName, userName, password);
                Server sqlServer = new Server(connection);
 
                Database db = sqlServer.Databases[databaseName];
                sqlRestore.Action = RestoreActionType.Database;
                String dataFileLocation = dataFilePath + databaseName + ".mdf";
                String logFileLocation = logFilePath + databaseName + "_Log.ldf";
                db = sqlServer.Databases[databaseName];
                RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
 
                sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
                sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFileLocation));
                sqlRestore.ReplaceDatabase = true;
                sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
                sqlRestore.PercentCompleteNotification = 10;
                sqlRestore.PercentComplete += new PercentCompleteEventHandler(sqlRestore_PercentComplete);
 
                sqlRestore.SqlRestore(sqlServer);
 
                db = sqlServer.Databases[databaseName];
 
                db.SetOnline();
 
                sqlServer.Refresh();
            }
            catch (SqlServerManagementException ex)
            {
                ex.Message.ToString();
            }
        }
 
        public event EventHandler<PercentCompleteEventArgs> PercentComplete;
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void sqlRestore_PercentComplete(object sender, PercentCompleteEventArgs e)
        {
            if (PercentComplete != null)
                PercentComplete(sender, e);
        }
 
        public event EventHandler<ServerMessageEventArgs> Complete;
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void sqlRestore_Complete(object sender, ServerMessageEventArgs e)
        {
            if (Complete != null)
                Complete(sender, e);
        }
    }
 
 /// <summary>
    ///
    /// </summary>
    public class BackupHelper
    {
        /// <summary>
        ///
        /// </summary>
        public BackupHelper()
        {
 
        }
        /// <summary>
        /// 备份数据库
        /// 涂聚文
        ///
        /// </summary>
        /// <param name="databaseName"></param>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <param name="serverName"></param>
        /// <param name="destinationPath"></param>
        public bool BackupDatabase(String databaseName, String userName, String password, String serverName, String destinationPath)
        {
            bool isok = false;
            try
            {
                Backup sqlBackup = new Backup();
 
                sqlBackup.Action = BackupActionType.Database;
                sqlBackup.BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString();
                sqlBackup.BackupSetName = "Archive";
 
                sqlBackup.Database = databaseName;
 
                BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
                ServerConnection connection = new ServerConnection(serverName, userName, password);
                Server sqlServer = new Server(connection);
 
                Database db = sqlServer.Databases[databaseName];
 
                sqlBackup.Initialize = true;
                sqlBackup.Checksum = true;
                sqlBackup.ContinueAfterError = true;
 
                sqlBackup.Devices.Add(deviceItem);
                sqlBackup.Incremental = false;
 
                sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
                sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
 
                sqlBackup.FormatMedia = false;
 
                sqlBackup.SqlBackup(sqlServer);
                isok = true;
            }
            catch (SqlServerManagementException ex)
            {
                ex.Message.ToString();
                isok = false;
            }
 
            return isok;
        }
    }

 

posted @   ®Geovin Du Dream Park™  阅读(469)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示