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:
Quote:
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.
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>