Best way to restore a database with FILESTREAM data using C# (SMO)

1

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;

  1. The restore function still looks for the path where the backup was generated from and fails
  2. The database backup contains FILESTREAM data and also fails because I can't seem to account for/or relocate that aswell.

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:

  1. How can I do this efficiently and also accounting for the filestream data
  2. What are my doing wrong with my 'RelocateFiles' function.

Thank you.!!

c#
.net
sql-server
winforms
asked on Stack Overflow Mar 27, 2020 by Manelaz • edited Apr 15, 2020 by Venkataraman R

1 Answer

0

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.

answered on Stack Overflow Apr 15, 2020 by Venkataraman R • edited Apr 15, 2020 by Venkataraman R

User contributions licensed under CC BY-SA 3.0