首页 新闻 搜索 专区 学院

SQLExpress winform 备份问题。

0
悬赏园豆:30 [已解决问题] 解决于 2010-06-01 10:40

现在在一个winform中有一个数据库备份的问题。

提示:在 sysdatabases 中找不到数据库 ''F:\SECURITYBILLING_1\SECURITYBILLING\DATABASE\SECURITYBILLING.MDF'' 所对应的条目。没有找到具有该名称的条目。请确保正确地输入了该名称。
BACKUP DATABASE 正在异常终止。

这个是连接字符串:Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database\Securitybilling.mdf;Integrated Security=True;User Instance=True"

别的一切正常使用没有问题。但是备份、还原不行。希望知道的互助一下。最好是贴出代码。

邢少的主页 邢少 | 专家六级 | 园豆:10926
提问于:2010-05-31 15:33
< >
分享
最佳答案
0
/*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();

}
}
}
}


也可以参考这儿:
http://www.sqldbatips.com/showarticle.asp?ID=27

 

或http://www.cnblogs.com/downmoon/archive/2009/08/20/1550850.html

收获园豆:30
邀月 | 高人七级 |园豆:25475 | 2010-05-31 22:18
我也已经解决问题了。就是你的这个方法。但是还是很感谢。 通过这个问题,有了一种感慨啊。 英文是中国程序员永远的痛。
邢少 | 园豆:10926 (专家六级) | 2010-06-01 10:39
其他回答(1)
0

为什么我也是想这样写的怎么不能用呀

报还原 对于 服务器“\\.\pipe\0768A80B-EC64-44\tsql\query”失败。

一世情缘 | 园豆:220 (菜鸟二级) | 2010-12-14 10:26
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册