View Code
1 using System; 2 using System.Data; 3 using System.Data.SqlClient; 4 5 namespace Commom 6 { 7 /// <summary> 8 /// 备份和还原sql server 2005数据库,在.net2.0中文正式版和sql server 2005系统上通过 9 /// </summary> 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 /// <summary> 24 /// 备份数据库 25 /// </summary> 26 /// <param name="databasename">要备份的数据源名称</param> 27 /// <param name="backuptodatabase">备份到的数据库文件名称及路径</param> 28 /// <returns></returns> 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 /// <summary> 105 /// 还原指定的数据库文件 106 /// </summary> 107 /// <param name="databasename">要还原的数据库</param> 108 /// <param name="databasefile">数据库备份文件及路径</param> 109 /// <returns></returns> 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 try 121 { 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 /// <summary> 137 /// 初始化数据库的连接 138 /// </summary> 139 /// <param name="strconn"></param> 140 private void init(string strconn) 141 { 142 conn = new SqlConnection(strconn); 143 144 } 145 } 146 } 147 148 --转自csdn某位高人