1 using System; 2 using System.Data; 3 using System.Data.SqlClient; 4 5 namespace Commom 6 { 7 /// 8 /// 备份和还原sql server 2005数据库,在.net2.0中文正式版和sql server 2005系统上通过 9 /// 10 public class BackupData 11 { 12 private SqlConnection conn; 13 public BackupData() 14 { 15 // 16 // TODO: 在此处添加构造函数逻辑 17 // 18 string sql = "data source=localhost;initial catalog=master;password=11;persist security info=True;user id=sa;workstation id=TOPS03496;packet size=4096";//注意默认数据库不要和恢复的数据库同名 19 20 init(sql); 21 } 22 23 /// 24 /// 备份数据库 25 /// 26 /// 要备份的数据源名称 27 /// 备份到的数据库文件名称及路径 28 /// 29 public bool BackUpDataBase(string databasename, string backuptodatabase) 30 { 31 string procname; 32 string name = databasename + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Date.Day.ToString() + DateTime.Now.Minute.ToString(); 33 string sql; 34 35 conn.Open(); //打开数据库连接 36 37 //删除逻辑备份设备，但不会删掉备份的数据库文件 38 procname = "sp_dropdevice"; 39 SqlCommand sqlcmd1 = new SqlCommand(procname, conn); 40 sqlcmd1.CommandType = CommandType.StoredProcedure; 41 42 SqlParameter sqlpar = new SqlParameter(); 43 sqlpar = sqlcmd1.Parameters.Add("@logicalname", SqlDbType.VarChar, 20); 44 sqlpar.Direction = ParameterDirection.Input; 45 sqlpar.Value = databasename; 46 47 try //如果逻辑设备不存在，略去错误 48 { 49 sqlcmd1.ExecuteNonQuery(); 50 } 51 catch 52 { 53 } 54 55 //创建逻辑备份设备 56 procname = "sp_addumpdevice"; 57 SqlCommand sqlcmd2 = new SqlCommand(procname, conn); 58 sqlcmd2.CommandType = CommandType.StoredProcedure; 59 60 sqlpar = sqlcmd2.Parameters.Add("@devtype", SqlDbType.VarChar, 20); 61 sqlpar.Direction = ParameterDirection.Input; 62 sqlpar.Value = "disk"; 63 64 65 sqlpar = sqlcmd2.Parameters.Add("@logicalname", SqlDbType.VarChar, 20);//逻辑设备名 66 sqlpar.Direction = ParameterDirection.Input; 67 sqlpar.Value = databasename; 68 69 sqlpar = sqlcmd2.Parameters.Add("@physicalname", SqlDbType.NVarChar, 260);//物理设备名 70 sqlpar.Direction = ParameterDirection.Input; 71 sqlpar.Value = backuptodatabase + name + ".bak"; 72 73 74 try 75 { 76 int i = sqlcmd2.ExecuteNonQuery(); 77 } 78 catch (Exception err) 79 { 80 string str = err.Message; 81 } 82 83 //备份数据库到指定的数据库文件(完全备份) 84 sql = "BACKUP DATABASE " + databasename + " TO " + databasename + " WITH INIT"; 85 SqlCommand sqlcmd3 = new SqlCommand(sql, conn); 86 sqlcmd3.CommandType = CommandType.Text; 87 try 88 { 89 sqlcmd3.ExecuteNonQuery(); 90 } 91 catch (Exception err) 92 { 93 string str = err.Message; 94 conn.Close(); 95 96 return false; 97 } 98 99 conn.Close();//关闭数据库连接 100 return true;101 102 }103 104 /// 105 /// 还原指定的数据库文件 106 /// 107 /// 要还原的数据库 108 /// 数据库备份文件及路径 109 /// 110 public bool RestoreDataBase(string databasename, string databasefile)111 {112 113 //还原指定的数据库文件 114 string sql = "RESTORE DATABASE " + databasename + " from DISK = ’" + databasefile + "’ ";115 SqlCommand sqlcmd = new SqlCommand(sql, conn);116 sqlcmd.CommandType = CommandType.Text;117 118 conn.Open();119 120 try121 {122 sqlcmd.ExecuteNonQuery();123 }124 catch (Exception err)125 {126 string str = err.Message;127 conn.Close();128 129 return false;130 }131 132 conn.Close();//关闭数据库连接 133 return true;134 }135 136 /// 137 /// 初始化数据库的连接 138 /// 139 /// 140 private void init(string strconn)141 {142 conn = new SqlConnection(strconn);143 144 }145 }146 }147 148 --转自csdn某位高人 
View Code