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
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.