0

Based on this article I made a small WPF application to backup/restore a database.

Code:

using System.Windows;
using Microsoft.SqlServer.Management.Smo;

namespace DBManager
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow
    {
        private Server srv;
        private Database db;
        private BackupDeviceItem bdi;
        private int recoverymod;

        public MainWindow()
        {
            InitializeComponent();
            srv = new Server();
            db = srv.Databases["MyDB"];
            recoverymod = (int) db.DatabaseOptions.RecoveryModel;
            bdi = new BackupDeviceItem("Test_Full_Backup1", DeviceType.File);
        }

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            BackupDB();
        }

        private void button2_Click(object sender, RoutedEventArgs e)
        {
            RestoreDB();
        }

        public void BackupDB()
        {
            var bk = new Backup
            {
                Action = BackupActionType.Database,
                BackupSetDescription = "Full backup of MyDB",
                BackupSetName = "MyDB Backup",
                Database = "MyDB"
            };

            bk.Devices.Add(bdi);
            bk.Incremental = false;
            bk.LogTruncation = BackupTruncateLogType.Truncate;
            bk.SqlBackup(srv);
            label1.Content = "Backup finished";
            bk.Devices.Remove(bdi);
        }

        public void RestoreDB()
        {
            db.Drop();
            var rs = new Restore { NoRecovery = true };
            rs.Devices.Add(bdi);
            rs.Database = "MyDB";
            rs.SqlRestore(srv);
            label1.Content = "Restoration finished";
            db = srv.Databases["MyDB"];
            rs.Devices.Remove(bdi);
            db.RecoveryModel = (RecoveryModel) recoverymod;
        }
    }
}

When I click button1 the backup file is created as expected.

When I click button2 I can see the "Restoration finished" message and everything looks OK but then I can't access the database with another application that uses it.

In SQL Management Studio I see "(Restoring...)" after the database's name and looks like the restoration process never finish.

Visual Studio 2010 and SQL Server 2012

  • There's already a tool that does this called SQL Server Management Studio. – Daniel Mann Aug 26 '13 at 15:51
  • I know but most target users don't have technical skills –  Aug 26 '13 at 15:53
  • So you want to give non-technical users access to restore SQL databases? That seems like a really bad idea. – Daniel Mann Aug 26 '13 at 15:56
  • The users will click a Backup button and choose where to store the backup file. If they need to recover from whatever then they click a Restore button and the database will be restored. That's all they will do. –  Aug 26 '13 at 16:00

1 Answers1

0

The database is left in a recovering state because you specified:

NoRecovery = true

If you want the database to be recovered after the restore, change this line to:

NoRecovery = false

More info in the Comparison of RECOVERY and NORECOVERY section here.

Bryan
  • 17,112
  • 7
  • 57
  • 80