Saturday, February 20, 2010

BACKUP AND RESTORE WITH USER INSTANCES

http://blogs.msdn.com/sqlexpress/archive/tags/Samples/default.aspx
Fresh thoughts concerning SQL Express:
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace CreateRANU
{
internal class Program
{
private static void Main(string[] args)
{
BackupDatabase();
RestoreBackup();
}

public static void BackupDatabase()
{
string sConnect = Properties.Settings.Default.BackupConnectionString;
string dbName;
using (var cnn = new SqlConnection(sConnect))
{
cnn.Open();
dbName = cnn.Database;
var sc = new ServerConnection(cnn);
var sv = new Server(sc);

// Check that I'm connected to the user instance
Console.WriteLine(sv.InstanceName);
// Create backup device item for the backup
var bdi = new BackupDeviceItem(@"C:\AppDataBackup\SampleBackup.bak", DeviceType.File);

// Create the backup informaton
var bk = new Backup();
bk.Devices.Add(bdi);
bk.Action = BackupActionType.Database;
bk.BackupSetDescription = "SQL Express is a great product!";
bk.BackupSetName = "SampleBackupSet";
bk.Database = dbName;
bk.ExpirationDate = new DateTime(2007, 5, 1);
bk.LogTruncation = BackupTruncateLogType.Truncate;

// Run the backup
bk.SqlBackup(sv);
Console.WriteLine("Your backup is complete.");
}
}

public static void RestoreBackup()
{
string sConnect = Properties.Settings.Default.BackupConnectionString;
string dbName;
using (var cnn = new SqlConnection(sConnect))
{
cnn.Open();
dbName = cnn.Database;
cnn.ChangeDatabase("master");
var sc = new ServerConnection(cnn);
var sv = new Server(sc);

// Check that I'm connected to the user instance
Console.WriteLine(sv.InstanceName);
// Create backup device item for the backup
var bdi = new BackupDeviceItem(@"C:\AppDataBackup\SampleBackup.bak", DeviceType.File);

// Create the restore object
var resDB = new Restore();
resDB.Devices.Add(bdi);
resDB.NoRecovery = false;
resDB.ReplaceDatabase = true;
resDB.Database = dbName;

// Restore the database
resDB.SqlRestore(sv);
Console.WriteLine("Your database has been restored.");
}
}

public static void CreateDatabase()
{
using (
var cn =
new SqlConnection(
@"Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True;Connection Timeout=60"))
{
cn.Open();
var cmd = new SqlCommand("CREATE DATABASE forumTest1", cn);
cmd.ExecuteNonQuery();
var cn2 =
new SqlConnection(
@"Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|\forumTest1.mdf;User Instance=True;Connection Timeout=60");
cn2.Open();
}
}
}
}

Powered by Blogger.