C＃使用microsoft.sqlserver.management.smo恢复数据库5（访问被拒绝） [英] C# restore database with microsoft.sqlserver.management.smo 5(access denied)
问题描述
嘿，
我正在尝试使用 Microsoft.SqlServer.Management.Smo 恢复数据库但是我一直拒绝访问。
我为实例设置了服务用户（MSSQL $ LOCAL2016），并拥有该文件夹的完全访问权限
文件夹是实例的默认文件夹（虽然我转到制作时不能说同样的内容）
我在C＃中创建文件夹
rstDatabase.SqlRestore（sqlServer）; 失败并显示错误：
Hey,
I am trying to restore a db with Microsoft.SqlServer.Management.Smo but I keep getting access denied.
I have set up the service user for the instance (MSSQL$LOCAL2016) with full access rights to the folder
The folder is the default folder for the instance (although the same cannot be said when I move to production)
I create the folders within C#
rstDatabase.SqlRestore(sqlServer); fails with the error:
操作系统在'C：\Database \ Rates.setuptest \ Data'上尝试'RestoreContainer :: ValidateTargetForCreation'时返回错误'5（访问被拒绝。）'。
文件'RateUploader'无法恢复为'C：\Database \ Rates.setuptest \ Data'。使用WITH MOVE识别文件的有效位置。
在'C：\Database上尝试'RestoreContainer :: ValidateTargetForCreation'时，操作系统返回错误'5（访问被拒绝。）' \ Rates.setuptest\Logs'。
文件'RateUploader_log'无法恢复为'C：\Database \ Rates.setuptest \ Logs'。使用WITH MOVE标识文件的有效位置。
计划RESTORE语句时发现问题。以前的消息提供了详细信息。
RESTORE DATABASE异常终止。
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Database\Rates.setuptest\Data'.
File 'RateUploader' cannot be restored to 'C:\Database\Rates.setuptest\Data'. Use WITH MOVE to identify a valid location for the file.
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Database\Rates.setuptest\Logs'.
File 'RateUploader_log' cannot be restored to 'C:\Database\Rates.setuptest\Logs'. Use WITH MOVE to identify a valid location for the file.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally.
似乎直接前进足够。我是否错过了显而易见的或者是否有一种巧妙的我不知道关闭？
谢谢^ _ ^
Andy
我尝试了什么：
Seems straight forward enough. Am I missing the obvious or is there a subtly I am not aware off?
Thanks ^_^
Andy
What I have tried:
public static bool RestoreDatabase(String databaseName)
{
MySqlConnectionStringBuilder stringBuilder = new MySqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["adminSqlAccess"].ConnectionString);
string serverName = stringBuilder.Server, userName = stringBuilder.UserID, password = stringBuilder.Password,
pFileLocation = string.Format(ConfigurationManager.AppSettings["dbPath"], databaseName, "Data"),
lFileLocation = string.Format(ConfigurationManager.AppSettings["dbPath"], databaseName, "Logs"),
bakFileLocation = ConfigurationManager.AppSettings["bakDbPath"];
if (!Directory.Exists(pFileLocation))
Directory.CreateDirectory(pFileLocation);
if (!Directory.Exists(lFileLocation))
Directory.CreateDirectory(lFileLocation);
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Restore rstDatabase = new Restore();
rstDatabase.Action = RestoreActionType.Database;
rstDatabase.Database = $"Rates.{databaseName}";
rstDatabase.RelocateFiles.Add(new RelocateFile("RateUploader", pFileLocation));
rstDatabase.RelocateFiles.Add(new RelocateFile("RateUploader_log", lFileLocation));
BackupDeviceItem bkpDevice = new BackupDeviceItem(bakFileLocation, DeviceType.File);
rstDatabase.Devices.Add(bkpDevice);
rstDatabase.ReplaceDatabase = true;
rstDatabase.SqlRestore(sqlServer);
return true;
}
配置：
Config:
<connectionStrings>
<add name="adminSqlAccess" connectionString="Data Source=.\LOCAL2016;Initial Catalog=SystemManager2.0;Persist Security Info=True;User ID=sa;Password=xxxxxxxx" />
</connectionStrings>
<appSettings>
<!-- ... -->
<add key="dbPath" value="C:\Database\{0}\{1}"/>
<add key="bakDbPath" value="C:\Database\blank\20170228-blank.bak"/>
</appSettings>
推荐答案
LOCAL2016）具有完全访问权限文件夹的权限
该文件夹是实例的默认文件夹（虽然我转到制作时不能说同样的内容）
我在C＃中创建文件夹
rstDatabase.SqlRestore（sqlServer）;失败并显示错误：
LOCAL2016) with full access rights to the folder
The folder is the default folder for the instance (although the same cannot be said when I move to production)
I create the folders within C#
rstDatabase.SqlRestore(sqlServer);fails with the error:
在'C：\\上尝试'RestoreContainer :: ValidateTargetForCreation'时，操作系统返回错误'5（访问被拒绝。）' 。
档‘Database\Rates.setuptest\Data RateUploader’不能恢复到‘C：\Database\Rates.setuptest\Data’。使用WITH MOVE识别文件的有效位置。
在'C：\Database上尝试'RestoreContainer :: ValidateTargetForCreation'时，操作系统返回错误'5（访问被拒绝。）' \ Rates.setuptest\Logs'。
文件'RateUploader_log'无法恢复为'C：\Database \ Rates.setuptest \ Logs'。使用WITH MOVE标识文件的有效位置。
计划RESTORE语句时发现问题。以前的消息提供了详细信息。
RESTORE DATABASE异常终止。
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Database\Rates.setuptest\Data'.
File 'RateUploader' cannot be restored to 'C:\Database\Rates.setuptest\Data'. Use WITH MOVE to identify a valid location for the file.
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Database\Rates.setuptest\Logs'.
File 'RateUploader_log' cannot be restored to 'C:\Database\Rates.setuptest\Logs'. Use WITH MOVE to identify a valid location for the file.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally.
似乎直接前进足够。我是否错过了显而易见的或者是否有一种巧妙的我不知道关闭？
谢谢^ _ ^
Andy
我尝试了什么：
Seems straight forward enough. Am I missing the obvious or is there a subtly I am not aware off?
Thanks ^_^
Andy
What I have tried:
public static bool RestoreDatabase(String databaseName)
{
MySqlConnectionStringBuilder stringBuilder = new MySqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["adminSqlAccess"].ConnectionString);
string serverName = stringBuilder.Server, userName = stringBuilder.UserID, password = stringBuilder.Password,
pFileLocation = string.Format(ConfigurationManager.AppSettings["dbPath"], databaseName, "Data"),
lFileLocation = string.Format(ConfigurationManager.AppSettings["dbPath"], databaseName, "Logs"),
bakFileLocation = ConfigurationManager.AppSettings["bakDbPath"];
if (!Directory.Exists(pFileLocation))
Directory.CreateDirectory(pFileLocation);
if (!Directory.Exists(lFileLocation))
Directory.CreateDirectory(lFileLocation);
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Restore rstDatabase = new Restore();
rstDatabase.Action = RestoreActionType.Database;
rstDatabase.Database =
Rates。{databaseName} ;
rstDatabase.RelocateFiles.Add（ new RelocateFile（ RateUploader，pFileLocation））;
rstDatabase.RelocateFiles.Add（ new RelocateFile（ RateUploader_log，lFileLocation））;
BackupDeviceItem bkpDevice = new BackupDeviceItem（bakFileLocation，DeviceType.File）;
rstDatabase.Devices.Add（bkpDevice）;
rstDatabase.ReplaceDatabase = true ;
rstDatabase.SqlRestore（sqlServer）;
return true ;
}
"Rates.{databaseName}"; rstDatabase.RelocateFiles.Add(new RelocateFile("RateUploader", pFileLocation)); rstDatabase.RelocateFiles.Add(new RelocateFile("RateUploader_log", lFileLocation)); BackupDeviceItem bkpDevice = new BackupDeviceItem(bakFileLocation, DeviceType.File); rstDatabase.Devices.Add(bkpDevice); rstDatabase.ReplaceDatabase = true; rstDatabase.SqlRestore(sqlServer); return true; }
配置：
Config:
<connectionStrings>
<add name="adminSqlAccess" connectionString="Data Source=.\LOCAL2016;Initial Catalog=SystemManager2.0;Persist Security Info=True;User ID=sa;Password=xxxxxxxx" />
</connectionStrings>
<appSettings>
<!-- ... -->
<add key="dbPath" value="C:\Database\{0}\{1}"/>
<add key="bakDbPath" value="C:\Database\blank\20170228-blank.bak"/>
</appSettings>
我这样做的方式是：在C＃中备份SQL数据库 [ ^ ] - 我通常会得到当我尝试保存备份时，反过来访问违规消息。然而，这可能是同样的问题 - SQL不会在您的用户ID下运行，因此目标文件夹需要适当的恢复权限。
The way I do it is here: Backing up an SQL Database in C#[^] - and I normally get the access violation message the other way round, when I try to save the backup. It's probably the same problem however - SQL doesn't run under your user ID, so the destination folder needs appropriate permissions for the restore.
这篇关于C＃使用microsoft.sqlserver.management.smo恢复数据库5（访问被拒绝）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
