locked
back up and restore database using C# RRS feed

  • Question

  • hi i am trying to restore database using C#,every thing go right,but when i open sql server  management  studio ,the database still remarked as restoring!
    but when i rightclick on it and chosse  task and restore and press ok,  the database  then became ready.
    this is my code
     
    for backup
    ///
    
    Server srv = new Server(@"myserver\MSSQLSERVER1");
                ServerConnection srvConn = srv.ConnectionContext;
               
                string strBackupDir = srv.Settings.BackupDirectory;
                DatabaseCollection objDatabases = srv.Databases;
                foreach (Database objDB in objDatabases)
                {
                    if (objDB.Name == "lawer")
                    {
                        if (objDB.IsSystemObject == false && objDB.IsMirroringEnabled == false)
                        {
                            DateTime datNow = DateTime.Now;
                            string strDBName = objDB.Name;
                            string strBKDate = datNow.ToString("yyyyMMddhhmmss");
                            Backup objBackup = new Backup();
                            objBackup.Action = BackupActionType.Database;
                            objBackup.Database = strDBName;
                            objBackup.BackupSetDescription = "Full backup of " + strDBName;
                            objBackup.BackupSetName = strDBName + " Backup";
                            objBackup.MediaDescription = "Disk";
                           DeviceType.File);
                            BackupDeviceItem bdi = new BackupDeviceItem("Test_Full_Backup1", DeviceType.File);
                            objBackup.Devices.Add(bdi);
                            objBackup.Incremental = false;
                            objBackup.LogTruncation = BackupTruncateLogType.Truncate;
                            objBackup.SqlBackup(srv);
                            MessageBox.Show("Full back up did");
                            objBackup.Devices.Remove(bdi);
                            if (objDB.DatabaseOptions.RecoveryModel != RecoveryModel.Simple)
                            {
                                datNow = DateTime.Now;
                                string strTBKDate = datNow.ToString("yyyyMMddhhmmss");
                                Backup objTrnBackup = new Backup();
                                objTrnBackup.Action = BackupActionType.Log;
                                objTrnBackup.BackupSetDescription = "Trans Log backup of " + strDBName;
                                objTrnBackup.BackupSetName = strDBName + " Backup";
                                objTrnBackup.Database = strDBName;
                                objTrnBackup.MediaDescription = "Disk";
                                objTrnBackup.Devices.AddDevice(strBackupDir + "\\" + strDBName + "_tlog_" + strTBKDate + ".trn", DeviceType.File);
                               
                                
                                objTrnBackup.SqlBackup(srv);
    
                            }
                        }
                    }
                }
            }
    
         for restoring
    ///
            {
                Server srv = new Server(@"myserver\MSSQLSERVER1");
                ServerConnection srvConn = srv.ConnectionContext;
                //srvConn.LoginSecure = true;
                string strBackupDir = srv.Settings.BackupDirectory;
                Restore rs = new Restore();
                rs.NoRecovery = true;
                BackupDeviceItem bdi = new BackupDeviceItem("Test_Full_Backup1", DeviceType.File);
                //rs.Devices.AddDevice(strBackupDir + "\\" + "Bdb" + "_db" + ".bak", DeviceType.File);
                rs.Devices.Add(bdi);
                rs.Database = "lawer";
                rs.ReplaceDatabase = true;
                rs.SqlRestore(srv);
                
                rs.NoRecovery = false;
                rs.Devices.Remove(bdi);
                DatabaseCollection ds=srv.Databases;
                foreach (Database a in ds)
                {
                    if (a.Name == "lawer")
                        a.DatabaseOptions.RecoveryModel = RecoveryModel.Full;
                }
                              
                MessageBox.Show("Restore All");
    
    
            }
    i wait your answers.
    • Moved by Harry Zhu Wednesday, August 19, 2009 2:11 AM relating to smo (From:Visual C# General)
    Monday, August 17, 2009 8:44 AM

Answers

  • Hi mhammad,

    There is another similar thread you may also refer to:

    http://social.technet.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/6dee14cf-b7e4-4b0f-8158-efb3041d4fe7
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Tuesday, August 25, 2009 10:16 AM
    Thursday, August 20, 2009 12:25 PM
  • Hi,

    I am assuming that this is for a simple full restore. That is, you are not restoring transaction logs after the full restore. The problem in the restore is with the property NoRecovery. This should be set to false, which is the default.

    NoRecovery allows you to keep the database available for restoring transaction logs for a roll-forward recovery.

    Hope this helps.

    Regards,

    Mark

    Edit: correction of term (install to restore) in second sentence.

    Be sure to de-spamify the email address to contact me...
    • Proposed as answer by MarkWHarrison Friday, August 21, 2009 12:16 AM
    • Marked as answer by KJian_ Tuesday, August 25, 2009 10:16 AM
    Friday, August 21, 2009 12:15 AM

All replies

  • I think you are forgetting to set db as Online and also not refreshing the server.

    db = sqlServer.Databases[databaseName];
    db.SetOnline();
    sqlServer.Refresh();
    Hope that helps..
    Regards,
    Mohib Sheth
    Life would have been much easier if I had the source-code !!
    Monday, August 17, 2009 8:50 AM
  • it not solve he problem
    Thursday, August 20, 2009 9:54 AM
  • Hello zoroo?

    If you facing a similar problem then Please, start a new thread :)

    Regards,
    Mohib Sheth
    Life would have been much easier if I had the source-code !!
    Thursday, August 20, 2009 10:28 AM
  • Hi mhammad,

    There is another similar thread you may also refer to:

    http://social.technet.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/6dee14cf-b7e4-4b0f-8158-efb3041d4fe7
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Tuesday, August 25, 2009 10:16 AM
    Thursday, August 20, 2009 12:25 PM
  • Hi,

    I am assuming that this is for a simple full restore. That is, you are not restoring transaction logs after the full restore. The problem in the restore is with the property NoRecovery. This should be set to false, which is the default.

    NoRecovery allows you to keep the database available for restoring transaction logs for a roll-forward recovery.

    Hope this helps.

    Regards,

    Mark

    Edit: correction of term (install to restore) in second sentence.

    Be sure to de-spamify the email address to contact me...
    • Proposed as answer by MarkWHarrison Friday, August 21, 2009 12:16 AM
    • Marked as answer by KJian_ Tuesday, August 25, 2009 10:16 AM
    Friday, August 21, 2009 12:15 AM
  • thank for every one write here 
    but the problem still here

    Saturday, August 22, 2009 8:32 AM
  • Hi zoroo,

    Are you the original poster of this thread? If you have the same problem, please open a thread and provide more details.

    Usually, this issue is caused by setting Restore.NoRecovery to true. There is a thread with the same cause:

    http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/11dfd5f1-9fde-443f-afd1-cd76a6861d0b


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, August 22, 2009 9:02 AM