我用C#备份了数据库，备份路径为c:\Text.bak
现在我要恢复数据库，代码如下：
protected void Button2_Click(object sender, EventArgs e)
{
string path = @"c:\Text.bak";
string restore = "restore database Text from disk='" + path + "';";
SqlConnection con = new SqlConnection("server=20090831-0852\\SQL2005;uid=sa;pwd=123");
SqlCommand cmd = new SqlCommand(restore, con);
try
{
con.Open();
cmd.ExecuteNonQuery();
this.Label1.Text = "恢复成功"; }
catch
{
this.Label1.Text = "恢复失败";
}
finally
{
con.Close();
}
}
问题一：执行了上面代码以后刷新sqlsever2005什么也没有啊？
问题二：Label1在执行代码后既不显示"恢复成功"，也不显示“恢复失败”！这到底是怎么一回事？
问题三：小弟刚刚接触数据库恢复，不晓得在执行上述代码之前应做什么准备工作，请您说一下？
希望各位逐个回答我的问题，越详细越好，否则兄弟看不明白，见笑了。谢谢！
现在我要恢复数据库，代码如下：
protected void Button2_Click(object sender, EventArgs e)
{
string path = @"c:\Text.bak";
string restore = "restore database Text from disk='" + path + "';";
SqlConnection con = new SqlConnection("server=20090831-0852\\SQL2005;uid=sa;pwd=123");
SqlCommand cmd = new SqlCommand(restore, con);
try
{
con.Open();
cmd.ExecuteNonQuery();
this.Label1.Text = "恢复成功"; }
catch
{
this.Label1.Text = "恢复失败";
}
finally
{
con.Close();
}
}
问题一：执行了上面代码以后刷新sqlsever2005什么也没有啊？
问题二：Label1在执行代码后既不显示"恢复成功"，也不显示“恢复失败”！这到底是怎么一回事？
问题三：小弟刚刚接触数据库恢复，不晓得在执行上述代码之前应做什么准备工作，请您说一下？
希望各位逐个回答我的问题，越详细越好，否则兄弟看不明白，见笑了。谢谢！
CREATE proc RestoreDb @bkfile nvarchar(1000),@dbname sysname='',@dbpath nvarchar(260)='',@retype nvarchar(10)='DB',@filenumber int=1, @overexist bit=1,@killuser bit=1
as declare @sql varchar(8000) if isnull(@dbname,'')=''select @sql=reverse(@bkfile),@sql=case when charindex('.',@sql)=0 then @sql else substring(@sql,charindex('.',@sql)+1,1000) end ,@sql=case when charindex('\',@sql)=0 then @sql else left(@sql,charindex('\',@sql)-1) end,@dbname=reverse(@sql)
set @sql='restore '+case @retype when 'LOG' then 'log ' else 'database ' end+@dbname+' from disk='''+@bkfile+''''+' with file='+cast(@filenumber as varchar) +case when @overexist=1 and @retype in('DB','DBNOR') then ',replace' else '' end +case @retype when 'DBNOR' then ',NORECOVERY' else ',RECOVERY' end
print @sql
if @overexist=1 and @killuser=1
begin declare @spid varchar(20) declare #spid cursor for select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname) open #spid fetch next from #spid into @spid while @@fetch_status=0 begin exec('kill '+@spid) fetch next from #spid into @spid End close #spid deallocate #spid End
exec (@sql)
http://www.cnblogs.com/efish/archive/2006/10/23/537336.html
当然，判断数据库是否存在以及新建数据库都是可以在代码里实现的。
public static string RestoreDatabase(string backfile)
{
///杀死原来所有的数据库连接进程
/// SqlConnection conn=new SqlConnection ();
conn.ConnectionString ="Data Source=.;Initial Catalog=master;User ID=sa;pwd =teny123";
conn.Open ();
///查找当前名为PUBS的数据库使用进程
string sql="SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='pubs'"; SqlCommand cmd1 = new SqlCommand(sql, conn);
SqlDataReader dr; ArrayList list = new ArrayList(); try
{
dr = cmd1.ExecuteReader();
while(dr.Read())
{
list.Add(dr.GetInt16(0));
}
dr.Close(); }
catch(SqlException eee)
{
MessageBox.Show (eee.ToString ());
}
finally
{
conn.Close ();
} MessageBox.Show (list.Count .ToString ());//循环杀死进程之;
for(int i = 0; i < list.Count; i++)
{
cmd1 = new SqlCommand(string.Format("KILL {0}", list[i].ToString ()), conn);
cmd1.ExecuteNonQuery(); MessageBox.Show ("you have killed thread "+list[i].ToString ()); } //这里一定要是master数据库，而不能是要还原的数据库，因为这样便变成了有其它进程
//占用了数据库。
string constr = @"Data Source=.;Initial Catalog=master;User ID=sa;pwd =teny123 ";
string database = "pubs";
string path = @"d:\1.mdf";
//string BACKUP = String.Format("backup database {0} to disk='{1}'", database, path);
string BACKUP = String.Format("RESTORE DATABASE {0} FROM DISK = '{1}'", database, path); SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand(BACKUP, con);
con.Open();
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("还原成功");
}
catch(SqlException ee)
{
//throw(ee); //MessageBox.Show("还原失败"); MessageBox.Show (ee.ToString ()); }
finally
{
con.Close();
}
return "成功与否字符串"; //return "restore successfully!";
}