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.Common;
using Microsoft.SqlServer.Management.Smo;
namespace CreateRANU
{
class Program
{
static void Main(string[] args)
{
BackupDatabase();
RestoreBackup();
}
public static void BackupDatabase()
{
string sConnect = Properties.Settings.Default.BackupConnectionString;
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 = Properties.Settings.Default.BackupConnectionString;
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();
}
}
}
}
Final Note:
This example performs a full backup and restore of the database. If you're doing a full backup because your original database is totally lost the logic to retrieve the database name is going to fail because you won't be able to connect to the database (which has been lost or corrupted) to get the name. It's an interesting logic question as to figure out how to determine the right way to restore a database for a user instance when you can't connect to the original to get the right auto name from the project. I have a few ideas, but I'm interested in how others might do this.
- Mike
Comments
Anonymous
March 22, 2007
Если Вы занимаетесь созданием настольного программного обеспечения, которому нужно обрабатывать не разрозненныеAnonymous
May 14, 2009
Thank you, The post was very helpful. Thanks AgainAnonymous
February 17, 2010
Will this method work to restore the database on Windows 7x64 with MS SQL Express x86? I do not work.Anonymous
February 19, 2010
RE: Express x86 on Win7x64 This method should work for any combination of Express/Windows architecture but you need to be sure you are using the correct architecture API for the code you're compiling. You'll likely want to be sure you've installed the x64 version of SNAC and the x64 version of SMO (assuming there is one), and then be sure to compile your applictaion to run on an x64 computer. You should be able to get further assistance in the MSDN forums in the SQL Data Access forum.
- Mike
Anonymous
August 15, 2010
thanks a lot it is very simple and fast , useful answer to meAnonymous
November 13, 2011
It thow "Restore failed for Server '.pipe5B002F7C-5CFE-4Ctsqlquery'." exception while i try restore database.Anonymous
March 22, 2014
parviz536@yahoo.com hi I do not use Sql Server Management but Just I use Sqlexpress how can backup via c#????Anonymous
May 23, 2014
Mr hasan : You should change your connectionString instead of the one in the code. in this part : string sConnect = Properties.Settings.Default.BackupConnectionString;Anonymous
May 23, 2014
By the way thanks alot for this awesome article ;)