实现数据库的备份: SqlConnection conn = new SqlConnection(connection); string databaseName=@"E:\Data\Test.mdf"; //指定数据库名称(完整路径) conn.Open(); string bakSQL = @"backup database @DataBaseName to disk=N'E:\Temp\Test.bak'"; //数据库备份SQL语句 try { SqlCommand cmd = new SqlCommand(bakSQL,conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@DataBaseName",databaseName); //参数 cmd.ExecuteNonQuery(); } catch (Exception ee) { Console.WriteLine(ee); }
数据库备份的还原:(待修改)
public static void Restore() { SqlConnection conn = new SqlConnection(connection); try { conn.Open(); //杀掉数据库连接的进程 SqlCommand killCmd = new SqlCommand("use master; SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='Test'", conn); SqlDataReader myreader = killCmd.ExecuteReader(); ArrayList list = new ArrayList(); while (myreader.Read()) { list.Add(myreader.GetInt16(0)); } myreader.Close(); for (int i = 0; i < list.Count; i++) { killCmd = new SqlCommand(string.Format("KILL{0}",list[i]),conn); killCmd.ExecuteNonQuery(); Console.WriteLine(string.Format("Kill线程{0}",i)); } //数据库文件恢复 string restoreSQL = @"restore database test from disk=N'E:\Temp\Test.bak' with move 'Test' to 'D:\\Data.mdf'";//此处为SQL恢复语句,报错,待修改 SqlCommand cmd = new SqlCommand(restoreSQL, conn); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } catch(Exception ee) { Console.WriteLine(ee); }
参考文章:http://topic.csdn.net/u/20070514/20/fa0a9389-5f0e-4c27-a6af-1a1b5120eb19.html
获取当前连接的SQL Server数据库名称:
public static void GetDataBaseName() { SqlConnection conn = new SqlConnection(connection); try { conn.Open(); string bakSQL = "select db_name(); "; //查询当前使用的数据库名字的SQL语句 SqlCommand cmd = new SqlCommand(bakSQL, conn); cmd.CommandType = CommandType.Text; SqlDataReader myreader = cmd.ExecuteReader(); if (myreader.Read()) { Console.WriteLine(myreader.GetString(0)); //在控制台输出 } else { Console.WriteLine("无结果"); } } catch(Exception ee) { Console.WriteLine(ee); } }
用SQL Server自带的存储过程,获取主机上所有数据库的名称,并将数据库名称的集合绑定到DropDownList控件上:
SqlConnection connection = new ConnString().GetSqlConn(); SqlCommand command = new SqlCommand("sp_helpdb", connection); command.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader reader = command.ExecuteReader(); ddlDatabaseList.DataSource = reader; ddlDatabaseList.DataTextField = "Name"; ddlDatabaseList.DataBind(); reader.Close(); connection.Close();
在上述实现数据库的恢复时候出错,希望老鸟给予指点!