C#备份和还原sql server数据库

  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某位高人