none
Restoring database problem

    Question

  • i have developed an application where in one form i placed two buttons, backup and restore. when i want to take backup from database to which i currenly login, i can .

    But when i want to restore database that bakup to database to which i currently login, i get an exception.

    So then add following method

    myConnection.Close()

    RestoreBackup();

    even though it is not restoring.

    the resore backup method is following

     

    public void RestoreBackup()

    {

    // If there was a SQL connection created

    if (srvSql == null)

    {

    Connect();

    }

     

     

    if (srvSql != null)

    {

    openBackupDialog.InitialDirectory = "./Backup";

    openBackupDialog.DefaultExt = "bak";

     

     

    // If the user has chosen the file from which he wants the database to be restored

    if (openBackupDialog.ShowDialog() == DialogResult.OK)

    {

    // Create a new database restore operation

    Restore rstDatabase = new Restore();

    // Set the restore type to a database restore

    rstDatabase.Action = RestoreActionType.Database;

     

     

    // Set the database that we want to perform the restore on

    rstDatabase.Database = "test";

     

     

    // Set the backup device from which we want to restore, to a file

    BackupDeviceItem bkpDevice = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);

     

     

    // Add the backup device to the restore type

    rstDatabase.Devices.Add(bkpDevice);

     

     

    // If the database already exists, replace it

    rstDatabase.ReplaceDatabase = true;

    rstDatabase.Complete += new Microsoft.SqlServer.Management.Common.ServerMessageEventHandler(rstDatabase_Complete);

    // Perform the restore

    try

    {

    rstDatabase.SqlRestore(srvSql);

    }

    catch (FailedOperationException ex)

    {

    //Log.WriteLine("");

    //Log.WriteLine("Error: (BackupManager.RestoreBackup)");

    //Log.Write(ex);

    MessageBox.Show("the Database which which u want to restore is in use kindly close connection first");

    throw new Exception("Error while restoring backup.", ex);

    }

    }

     

     

    }

    else

    {

    throw new Exception("Could not connect to database.");

    }

     

     

    }

    Tuesday, February 06, 2007 11:11 AM

Answers

  • Hi,

    drop all connections is listed in the blog. The default database can be tweaked by either changing the default database of the user, or using it directly within the connection string -> Initial Catalog=master

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Tuesday, February 06, 2007 2:28 PM
    Moderator

All replies

  • YOu will either have to drop all existing connections to the database first. Current Connections won´t be dropped automatically, see my post on the blog for this issue concerning DROP DATABASE:

    http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Tuesday, February 06, 2007 1:33 PM
    Moderator
  • Two things you must remember when attempting to restore:  1) you must have sysadmin or dbcreator privileges, and 2) you cannot be connected to the database you're attempting to restore.  Set your current database to 'master' before issuing the restore command.

    Tuesday, February 06, 2007 1:37 PM
  • How to drop all connections and

    How to master,

    Thanks

    Tuesday, February 06, 2007 2:14 PM
  • Hi,

    drop all connections is listed in the blog. The default database can be tweaked by either changing the default database of the user, or using it directly within the connection string -> Initial Catalog=master

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Tuesday, February 06, 2007 2:28 PM
    Moderator
  • Thank you sir, you really helped me, my problem solved by following steps


            public void RestoreBackup()
            {
               Global.con.Close();   // connection of my database for normal transaction

                string databaseName = "test"; //database which i want to restore from test.bak
           

                if (srvSql == null)       // Server
                {
                   
                    Connect1();          //Opening a new connection (conForMasterDatabase) to master database

                }


                srvSql.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));

         ..................

    //// restore database codes

    ..............................

       Global.conForMasterDatabase.Close();

       Global.con.Open(); // reopen connection for normal use

    ///Global is my class

    }

     

    Now please tell me how to restore and backup from remote location

    Tuesday, February 06, 2007 3:18 PM