C#代码备份还原MSSQL数据库

备份数据库

[c-sharp] view plain copy print ?

1. /// <summary> 
2.        /// 备份配置文件config.xml中数据库 
3.        /// </summary> 
4.        /// <param name="backupFolder">备份文件路径</param> 
5.        /// <returns></returns> 
6.        public static bool DataBackupConfigDB(string backupFolder) 
7.        { 
8.            //获取配置文件中sql数据库名 
9.            string dbName = "SqlDB"; 
10.            string name = dbName + DateTime.Now.ToString("yyyyMMddHHmmss"); 
11.            string procname; 
12.            string sql; 
13.            //创建连接对象 
14.            SqlConnection conn = new SqlConnection(GetConnStr()); 
15.            conn.Open();        //打开数据库连接 
16.            //删除逻辑备份设备，但不会删掉备份的数据库文件 
17.            procname = "sp_dropdevice"; 
18.            SqlCommand sqlcmd1 = new SqlCommand(procname, conn); 
19.            sqlcmd1.CommandType = CommandType.StoredProcedure; 
20.            SqlParameter sqlpar = new SqlParameter(); 
21.            sqlpar = sqlcmd1.Parameters.Add("@logicalname", SqlDbType.VarChar, 20); 
22.            sqlpar.Direction = ParameterDirection.Input; 
23.            sqlpar.Value = dbName; 
24.            try        //如果逻辑设备不存在，略去错误 
25.            { 
26.                sqlcmd1.ExecuteNonQuery(); 
27.            } 
28.            catch 
29.            { 
30.                MessageBox.Show("错误的备份文件目录"); 
31.            } 
32.            //创建逻辑备份设备 
33.            procname = "sp_addumpdevice"; 
34.            SqlCommand sqlcmd2 = new SqlCommand(procname, conn); 
35.            sqlcmd2.CommandType = CommandType.StoredProcedure; 
36.            sqlpar = sqlcmd2.Parameters.Add("@devtype", SqlDbType.VarChar, 20); 
37.            sqlpar.Direction = ParameterDirection.Input; 
38.            sqlpar.Value = "disk"; 
39.            sqlpar = sqlcmd2.Parameters.Add("@logicalname", SqlDbType.VarChar, 20);//逻辑设备名 
40.            sqlpar.Direction = ParameterDirection.Input; 
41.            sqlpar.Value = dbName; 
42.            sqlpar = sqlcmd2.Parameters.Add("@physicalname", SqlDbType.NVarChar, 260);//物理设备名 
43.            sqlpar.Direction = ParameterDirection.Input; 
44.            sqlpar.Value = backupFolder + name + ".bak"; 
45.            try 
46.            { 
47.                int i = sqlcmd2.ExecuteNonQuery(); 
48.            } 
49.            catch (Exception err) 
50.            { 
51.                string str = err.Message; 
52.            } 
53.            //备份数据库到指定的数据库文件(完全备份) 
54.            sql = "BACKUP DATABASE " + dbName + " TO " + dbName + " WITH INIT"; 
55.            SqlCommand sqlcmd3 = new SqlCommand(sql, conn); 
56.            sqlcmd3.CommandType = CommandType.Text; 
57.            try 
58.            { 
59.                sqlcmd3.ExecuteNonQuery(); 
60.            } 
61.            catch (Exception err) 
62.            { 
63.                string str = err.Message; 
64.                conn.Close(); 
65.                return false; 
66.            } 
67.            conn.Close();//关闭数据库连接 
68.            return true; 
69.        } 

/// <summary> /// 备份配置文件config.xml中数据库 /// </summary> /// <param name="backupFolder">备份文件路径</param> /// <returns></returns> public static bool DataBackupConfigDB(string backupFolder) { //获取配置文件中sql数据库名 string dbName = "SqlDB"; string name = dbName + DateTime.Now.ToString("yyyyMMddHHmmss"); string procname; string sql; //创建连接对象 SqlConnection conn = new SqlConnection(GetConnStr()); conn.Open(); //打开数据库连接 //删除逻辑备份设备，但不会删掉备份的数据库文件 procname = "sp_dropdevice"; SqlCommand sqlcmd1 = new SqlCommand(procname, conn); sqlcmd1.CommandType = CommandType.StoredProcedure; SqlParameter sqlpar = new SqlParameter(); sqlpar = sqlcmd1.Parameters.Add("@logicalname", SqlDbType.VarChar, 20); sqlpar.Direction = ParameterDirection.Input; sqlpar.Value = dbName; try //如果逻辑设备不存在，略去错误 { sqlcmd1.ExecuteNonQuery(); } catch { MessageBox.Show("错误的备份文件目录"); } //创建逻辑备份设备 procname = "sp_addumpdevice"; SqlCommand sqlcmd2 = new SqlCommand(procname, conn); sqlcmd2.CommandType = CommandType.StoredProcedure; sqlpar = sqlcmd2.Parameters.Add("@devtype", SqlDbType.VarChar, 20); sqlpar.Direction = ParameterDirection.Input; sqlpar.Value = "disk"; sqlpar = sqlcmd2.Parameters.Add("@logicalname", SqlDbType.VarChar, 20);//逻辑设备名 sqlpar.Direction = ParameterDirection.Input; sqlpar.Value = dbName; sqlpar = sqlcmd2.Parameters.Add("@physicalname", SqlDbType.NVarChar, 260);//物理设备名 sqlpar.Direction = ParameterDirection.Input; sqlpar.Value = backupFolder + name + ".bak"; try { int i = sqlcmd2.ExecuteNonQuery(); } catch (Exception err) { string str = err.Message; } //备份数据库到指定的数据库文件(完全备份) sql = "BACKUP DATABASE " + dbName + " TO " + dbName + " WITH INIT"; SqlCommand sqlcmd3 = new SqlCommand(sql, conn); sqlcmd3.CommandType = CommandType.Text; try { sqlcmd3.ExecuteNonQuery(); } catch (Exception err) { string str = err.Message; conn.Close(); return false; } conn.Close();//关闭数据库连接 return true; }

还原数据库时如果使用

RESTORE DATABASE dbName（数据库名） from DISK = 'e:/' to replace

sql错误提示：RESTORE 无法处理数据库dbName 因为它正由此会话使用 建议在执行此操作时使用 master 数据库

在网上终于找到了解决方法，具体代码如下：

[c-sharp] view plain copy print ?

1. /// <summary> 
2. /// 还原数据库文件 
3. /// </summary> 
4. /// <param name="dbFile">数据库备份文件（含路径）</param> 
5. /// <returns></returns> 
6. public static bool DataRestoreConfigDB(string dbFile) 
7. { 
8.     //sql数据库名 
9.     string dbName = "SqlDB"; 
10.     //创建连接对象 
11.     SqlConnection conn = new SqlConnection(GetConnStr()); 
12.     //还原指定的数据库文件 
13.     string sql =string.Format("use master ;declare @s varchar(8000);select @s=isnull(@s,'')+' kill '+rtrim(spID) from master..sysprocesses where dbid=db_id('{0}');select @s;exec(@s) ;RESTORE DATABASE {1} FROM DISK = N'{2}' with replace",dbName,dbName,dbFile); 
14.     SqlCommand sqlcmd = new SqlCommand(sql, conn); 
15.     sqlcmd.CommandType = CommandType.Text; 
16.     conn.Open(); 
17.     try 
18.     { 
19.         sqlcmd.ExecuteNonQuery(); 
20.     } 
21.     catch (Exception err) 
22.     { 
23.         string str = err.Message; 
24.         conn.Close(); 
25.         return false; 
26.     } 
27.     conn.Close();//关闭数据库连接 
28.     return true; 
29. } 

http://blog.csdn.net/dinid/article/details/6216767 转载地址