数据库备份问题

-- 备份数据库
backup database db_CSManage to disk='c:\backup.bak'
-- 还原数据库，必须先备份该数据库的日志文件到原先的备份文件中
backup log db_CSManage to disk='c:\backup.bak'
restore database db_CSManage from disk='c:\backup.bak'

    其中db_CSManage是数据库名称，disk后的路径即是备份文件存储的路径。
知道了SQL语句，那么在.NET代码中就容易多了，备份的.NET代码如下：

 

当恢复模型为 SIMPLE 时,不允许使用 BACKUP LOG 语句

解决方法:

 

进 入MSSQL的企业管理器,选择相应的数据库实体,点击右键选择"属性",选择"选项"标签页,把故障还原模型修改成"大容量日志记录的",然后重新备份 一下!

try
{
	if (txtPath.Text != "" && txtName122.Text != "")
	{
		getSqlConnection geCon = new getSqlConnection();
		SqlConnection con = geCon.GetCon();

		string strBacl = "backup database db_CSManage to disk='" + txtPath.Text.Trim() + "\\" + txtName.Text.Trim() + ".bak'";
		SqlCommand Cmd = new SqlCommand(strBacl, con);
		if (Cmd.ExecuteNonQuery() != 0)
		{
			MessageBox.Show("数据备份成功！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
			this.Close();
		}
		else
		{
			MessageBox.Show("数据备份失败！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
		}

	}
	else
	{
		MessageBox.Show("请填写备份的正确位置及文件名！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);

	}// end 
}
catch (Exception ee)
{
	MessageBox.Show(ee.Message.ToString());
}

 

以下是还原数据库的代码，在示例中发现开头得先删除与该数据库相关的进程，然后在还原之前得先把数据库日志备份到开始的备份文件中，然后才还原备 份文件，要不然会出错的，代码如下：

if (textPaht.Text != "")
{
	getSqlConnection geCon = new getSqlConnection();
	SqlConnection con = geCon.GetCon();
	if (con.State == ConnectionState.Open)
	{
		con.Close();
	}
	string DateStr = "Data Source=niunan\\sqlexpress;Database=master;User id=sa;PWD=123456";
	SqlConnection conn = new SqlConnection(DateStr);
	conn.Open();

	//-------------------杀掉所有连接 db_CSManage 数据库的进程--------------
	string strSQL = "select spid from master..sysprocesses where dbid=db_id( 'db_CSManage') ";
	SqlDataAdapter Da = new SqlDataAdapter(strSQL, conn);

	DataTable spidTable = new DataTable();
	Da.Fill(spidTable);

	SqlCommand Cmd = new SqlCommand();
	Cmd.CommandType = CommandType.Text;
	Cmd.Connection = conn;

	for (int iRow = 0; iRow <= spidTable.Rows.Count - 1; iRow++)
	{
		Cmd.CommandText = "kill " + spidTable.Rows[iRow][0].ToString();   //强行关闭用户进程 
		Cmd.ExecuteNonQuery();
	}
	conn.Close();
	conn.Dispose();
	//--------------------------------------------------------------------

	SqlConnection sqlcon = new SqlConnection(DateStr);
	sqlcon.Open();
	string sql = "backup log db_CSManage to disk='" + textPaht.Text.Trim() + "' restore database db_CSManage from disk='" + textPaht.Text.Trim() + "'";
	SqlCommand sqlCmd = new SqlCommand(sql, sqlcon);
	sqlCmd.ExecuteNonQuery();
	sqlCmd.Dispose();
	sqlcon.Close();
	sqlcon.Dispose();
	MessageBox.Show("数据还原成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
	MessageBox.Show("为了必免数据丢失，在数据库还原后将关闭整个系统。");
	Application.Exit();
}
else
{
	MessageBox.Show("请选择备份文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}

 

因为数据库正在使用,所以未能获得对数据库的排它访问权的解决方法（转自百度空间）。

在备份按钮里写：

   protected void Button1_Click(object sender, EventArgs e)
    {
         string path = "e:\\MAZ数据库备份\\" + Menu+ ".bak";
                if (File.Exists(path))
                {
                   File.Delete(path);//注意，这个步骤很重要，如果重复，在备份的数据，就会变成，

//你刚开始的数据,所以每次都要先删除．

　　　　　　}
                if (!File.Exists(path))
                {
                    FileStream fs = File.Create(path);

                    fs.Close();
                }
        string backupstr="backup database Test to disk='"+path+"';";
        SqlConnection con = new SqlConnection("server=localhost;database=Menu;uid=sa;pwd=sa;");
        SqlCommand cmd = new SqlCommand(backupstr, con);
        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
            MessageBox.Show("备份成功！");
               connection.Close();

        }
       catch (Exception ex)
                {
                    string stringError = ex.ToString();
                    MessageBox.Show("备份失败！");
                    connection.Close();
                }  
    ｝

 

在还原按钮里写：

protected void Button2_Click(object sender, EventArgs e)
    {
        string path = "e:\\MAZ数据库备份\\" + Menu+ ".bak";
              

string connectionStringTest = "server=localhost ;database=master;uid=sa;pwd=sa";
             
                SqlConnection connection = new SqlConnection(connectionStringTest);
                string backupstr = "restore database Menu from disk='" + path + "';";         

try
                {
                    string sql = "exec    killspid '" + Menu+ "'";//这个很关键，要不然就出现题目上的错误了
                   SqlCommand cmd = new SqlCommand(sql, connection);
                   connection.Open();
                  
                   cmd.ExecuteNonQuery();
                   cmd = new SqlCommand(backupstr, connection);
                   cmd.ExecuteNonQuery();
                    MessageBox.Show("恢复成功！");
                    connection.Close();
                }
                catch (Exception ex)
                {
                    string stringError = ex.ToString();
                    MessageBox.Show("恢复失败！");
                    connection.Close();
                }


    }

 存储过程　killspid 

create    proc    killspid    (@dbname    varchar(20))     
as     
begin     
declare    @sql    nvarchar(500)     
declare    @spid    int     
set    @sql='declare    getspid    cursor    for         
select    spid    from    sysprocesses    where    dbid=db_id('''+@dbname+''')'     
exec    (@sql)     
open    getspid     
fetch    next    from    getspid    into    @spid     
while    @@fetch_status    <    >-1     
begin     
exec('kill    '+@spid)     
fetch    next    from    getspid    into    @spid     
end     
close    getspid     
deallocate    getspid     
end     


GO