none
SqlServer 2005 Database backup and Restore RRS feed

  • Question

  • I developed a windows application to facilitate database backup and restore. The backup works fine and when it comes to restore it says "Cannot drop database "test1" because it is currently in use.".

    Here is my backup code snippet,

    private
    void btngetBackup_Click(object sender, EventArgs e)
    {
        try
        
    {
            SqlConnection conTest1 = new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=test1;Integrated Security=True");
            conTest1.Open();

            SqlCommand
    cmdCreateBak = new SqlCommand("BACKUP DATABASE test1 TO DISK=@Location", conTest1);
            cmdCreateBak.Parameters.AddWithValue(
    "@Location", lblBakupPath.Text);
            int i = cmdCreateBak.ExecuteNonQuery();
            cmdCreateBak.Dispose();
            conTest1.Dispose();
            MessageBox.Show(i.ToString()); // Returns "-1"
            lblStatus.Text =
    "Database bakup succeeded!";
        }
    }

    Restore coding,

    private
    void btnRestore_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection conMaster = new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=master;Integrated Security=True");
        conMaster.Open();
        SqlCommand cmdCreateDB = new SqlCommand("IF EXISTS (SELECT name FROM sys.databases WHERE name = N'test1') DROP DATABASE [test1]", conMaster);
        int i = cmdCreateDB.ExecuteNonQuery();
        MessageBox.Show(i.ToString());
        cmdCreateDB.Dispose();
        conMaster.Close();

           SqlConnection conTest1 = new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=master;Integrated Security=True");
        conTest1.Open();
        SqlCommand cmdRestore = new SqlCommand("Restore database test1 from disk=@Location", conTest1);
        cmdRestore.Parameters.AddWithValue(
    "@Location", lblRestorePath.Text);
        int ii = cmdRestore.ExecuteNonQuery();
        MessageBox.Show(ii.ToString());
        lblStatus.Text =
    "Database Restored succeeded!";
    }

    When it tries to drop the database test1, error is fired saying "Cannot drop database "test1" because it is currently in use."

    Any help would be really appreciated. Thanks in advance.

    Sunday, July 27, 2008 5:23 AM

Answers