/*Tuesday, March 20, 2007 4:14 PM sqlexpress 
Backup and Restore with User Instances 
Backup and Restore is an important part of any application, but when you're writing an application for an end-user, it becomes even more important that you do a good job of handling Backup and Restore in your code since you end-user will likely not know much about being a Database Administrator. Add to that the fact that we don't include SQL Agent or the Maintenance Plan wizard in SQL Express, and you have the perfect reason to roll your own solution for Backup and Restore. 

SQL Management Objects (SMO) offers two classes with the likely name of Backup and Restore that can help you handle this in your application. These objects are documented in Books Online and you can even find a sample of their usage here. One thing you won't find in the BOL example is information about how user instances impact the usage of these classes. Luckily, you have this blog and the help of your friends on the SQL Express forum. 

Mfriedlander started a couple threads on the forum related to backing up and restoring a database in a user instance. The trick with user instances is that the databases are typically auto named at runtime based on the path to the database file that is embedded into your project. VS and SQL Express work together using the |DataDirecotry| macro as part of the connection string to determine where the database is and how to dynamically name it. If you are using ClickOnce deployment, which is kind of the whole point of user instances, there are a number of things that will cause the location of the database to change, which means the database name will change over time. (This also has the result of not allowing you to give you database a static name using either Initial Catalog= or Database= in your connection string because the changing location of the database would cause a naming conflict, but that's a different post.) 

For the backup scenario, the workaround is fairly straight forward, you need to connect to the database using the VS created connection string and then return the name of the database from the connection. The restore version proved a bit more interesting. You can use the same trick of connecting to the database using the VS created connection string to get the database name, but that connection to the database causes a failure when you attempt to Restore because SMO cannot get an exclusive lock on the database. The solution turns out to be straight forward, simply change the database context of the connection you've opened with the ChangeDatabase method of the Connection object. I've created a C# sample that creates a full backup of an embedded database and then calls Restore on the same database. If you'd like to see the similar operation done in VB.NET, check out the forum posts that I've linked above. 
*/
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;



namespace CreateRANU
{
    class Program
    {
        static void Main(string[] args)
        {
            string strCon = "";
            BackupDatabase(strCon);
            RestoreBackup(strCon);
        }

        public static void BackupDatabase(string sConnect)
        {
            string dbName;

            using (SqlConnection cnn = new SqlConnection(sConnect))
            {
                cnn.Open();
                dbName = cnn.Database.ToString();

                ServerConnection sc = new ServerConnection(cnn);
                Server sv = new Server(sc);

                // Check that I'm connected to the user instance
                Console.WriteLine(sv.InstanceName.ToString());

                // Create backup device item for the backup
                BackupDeviceItem bdi = new BackupDeviceItem(@"C:\AppDataBackup\SampleBackup.bak", DeviceType.File);

                // Create the backup informaton
                Backup 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)
        { 
            string dbName;

            using (SqlConnection cnn = new SqlConnection(sConnect))
            {
                cnn.Open();
                dbName = cnn.Database.ToString();
                cnn.ChangeDatabase("master");

                ServerConnection sc = new ServerConnection(cnn);
                Server sv = new Server(sc);

                // Check that I'm connected to the user instance
                Console.WriteLine(sv.InstanceName.ToString());

                // Create backup device item for the backup
                BackupDeviceItem bdi = new BackupDeviceItem(@"C:\AppDataBackup\SampleBackup.bak", DeviceType.File);

                // Create the restore object
                Restore 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 (SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True;Connection Timeout=60"))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand("CREATE DATABASE forumTest1", cn);
                cmd.ExecuteNonQuery();

                SqlConnection cn2 = new SqlConnection(@"Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|\forumTest1.mdf;User Instance=True;Connection Timeout=60");
                cn2.Open();

            }        
        }
    }
}