I am writing a simple C# application that'll allow users restore a databases by selecting path to the db and supplying a new name to the database - my problems are;
The code C#
Restore dbRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
dbRestore.Devices.Add(deviceItem);
dbRestore.Database = databaseName;
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
dbRestore.Action = RestoreActionType.Database;
String dataFileLocation = dataFilePath + databaseName + ".mdf";
String logFileLocation = logFilePath + databaseName + "_Log.ldf";
db = sqlServer.Databases[databaseName];
DataTable dtFileList = dbRestore.ReadFileList(sqlServer);
string dbLogicalName = dtFileList.Rows[0][0].ToString();
string logLogicalName = dtFileList.Rows[1][0].ToString();
dbRestore.RelocateFiles.Add(new RelocateFile(dbLogicalName, dataFileLocation));
dbRestore.RelocateFiles.Add(new RelocateFile(logLogicalName + "_log", logFileLocation));
dbRestore.ReplaceDatabase = true;
dbRestore.dbRestore(sqlServer);
db = sqlServer.Databases[databaseName];
db.SetOnline();
sqlServer.Refresh();
}
catch (Exception ex)
{
Console.Write(ex.ToString());
MessageBox.Show(ex.Message, "This is exception for testing");
}
The ##error
Exception thrown: 'Microsoft.SqlServer.Management.Smo.FailedOperationException' in Microsoft.SqlServer.SmoExtended.dll Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'SERVERNAME'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Logical file 'DatabaseDemo_4do6_EM_INDEXGROUP_log' is not part of database 'NewDatabaseName'. Use RESTORE FILELISTONLY to list the logical file names. RESTORE DATABASE is terminating abnormally. at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry) at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries) at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
--- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv) The program '[14052] OPSAdmin.exe' has exited with code -1 (0xffffffff).
Question:
Thank you.!!
You need to define separate variable for holding the Filestream filegroup name.
String fileStreamFGPhysicalPath = dataFilePath + databaseName;
string fileStreamFGLogicalName;
string dbLogicalName;
string logLogicalName;
You need to loop through the datatable to identify filestream logicalName. You can identify it using FileType column.
DataTable dtFileList = dbRestore.ReadFileList(sqlServer);
Foreach(DataRow dr in dtFileList)
{
switch (dr["Type"])
{
case "D":
dbLogicalName = dr["LogicalName"].ToString();
break;
case "L":
logLogicalName = dr["LogicalName"].ToString();
break;
case "S":
fileStreamFGLogicalName= dr["LogicalName"].ToString();
break;
}
}
Now, define relocate file for filestream also.
dbRestore.RelocateFiles.Add(new RelocateFile(dbLogicalName, dataFileLocation));
dbRestore.RelocateFiles.Add(new RelocateFile(logLogicalName + "_log", logFileLocation));
dbRestore.RelocateFiles.Add(new RelocateFile(fileStreamFGLogicalName , fileStreamFGPhysicalPath ));
Note: I have considered only three files here: data, log, filestream. You could be having multiple data files. So, you need to make the datafile names as arrays and move them accordingly in the Relocatefiles.
User contributions licensed under CC BY-SA 3.0