通过AttachDBFilename备份还原sql数据库 [英] backup restore sql database through AttachDBFilename

问题描述

我无法创建保存在C:\database\mydb.mdf

错误:无法创建备份

error : Unable to create a backup

        Backup sqlBackup = new Backup();

        sqlBackup.Action = BackupActionType.Database;
        sqlBackup.BackupSetDescription = "ArchiveDataBase:" +
                                         DateTime.Now.ToShortDateString();
        sqlBackup.BackupSetName = "Archive";

        sqlBackup.Database = databaseName;

        BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
        //ServerConnection connection = new ServerConnection(serverName, userName, password);
        ServerConnection connection = new ServerConnection(serverName);

        Server sqlServer = new Server(connection);

        Database db = sqlServer.Databases[databaseName];

        sqlBackup.Initialize = true;
        sqlBackup.Checksum = true;
        sqlBackup.ContinueAfterError = true;

        sqlBackup.Devices.Add(deviceItem);
        sqlBackup.Incremental = false;

        sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
        sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

        sqlBackup.FormatMedia = false;

        sqlBackup.SqlBackup(sqlServer);   

 string dataBaseName = @"C:\database\mydb.mdf";    
 string serverName = @"Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True";    
 string destinationPath = "C:\\mydb.bak";    

也许我传递了错误的变量?

Maybe I am passing wrong variables?

请任何人都可以验证并将其发布给我正确的解决方案

Please can anyone verify it and post me the right solution

thnx.

PS:数据库不受密码保护，可以使用混合身份验证

PS: database is not password protected and can use mixed authentication

推荐答案

首先-我猜您的某些参数有误:

First of all - I guess some of your parameters are wrong:

ServerConnection connection = new ServerConnection(serverName);

在这里，您需要传递只是服务器的名称-因此，在这种情况下，请勿发送整个连接字符串-仅发送.\SQLExpress

Here, you need to pass just the server's name - so in your case, do not send in your whole connection string - just .\SQLExpress

关于您的数据库名称-我不知道您是否可以使用SMO备份SQL Server中的附加" MDF文件-通常，这将是数据库名称(仅数据库名称-无文件名，无扩展名)，而数据库位于服务器上的 上.

As for your database name - I don't know if you can use SMO to backup an "attached" MDF file in SQL Server - normally, this would be the database name (the name of the database only - no file name, no extension) when the database is on the server.

string dataBaseName = @"C:\database\mydb.mdf";    

所以我的建议是:

• 将此MDF文件附加到您的SQL Server实例(无论如何都已安装)
• 给它起一个有意义的名称，例如mydb

• 然后在此处使用仅数据库名称作为您的值:

• attach this MDF file to your SQL Server instance (which you have installed anyway)
• give it a meaningful name, e.g. mydb

• then use just the database name as your value here:

string dataBaseName = "mydb";      

有了这些要点，就我而言，您的代码确实可以正常工作，至少...

With these points in place, your code does work just fine in my case, at least...

这篇关于通过AttachDBFilename备份还原sql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！