备份数据库
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 }
还原数据库时如果使用RESTORE DATABASE dbName（数据库名） from DISK = 'e:\' to replace
sql错误提示：RESTORE 无法处理数据库dbName 因为它正由此会话使用 建议在执行此操作时使用 master 数据库
在网上终于找到了解决方法，具体代码如下：
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 }
