how to restore using restore class of Microsoft.SqlServer.Management.Smo namespace

Question

public void  RestoreDatabase(String databaseName, String filePath, 
            String serverName, String userName, String password, String dataFilePath, String logFilePath)
{
    Restore sqlRestore = new Restore();
    BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
    sqlRestore.Devices.Add(deviceItem);
    sqlRestore.Database = databaseName;
    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);
    Database db = sqlServer.Databases[databaseName];
    sqlRestore.Action = RestoreActionType.Database;
    String dataFileLocation = dataFilePath;
    String logFileLocation = logFilePath; 
    db = sqlServer.Databases[databaseName];
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFileLocation));
    sqlRestore.ReplaceDatabase = true;
    sqlRestore.Complete +=new ServerMessageEventHandler(sqlRestore_Complete);
    sqlRestore.SqlRestore(sqlServer);
    db = sqlServer.Databases[databaseName];
    db.SetOnline();
    sqlServer.Refresh();
}

On calling this method the restore operation failed with this message

Restore failed for Server 'MDM04\SQLEXPRESS'.

mdm04 is my computer name

the inner exception is

"System.Data.SqlClient.SqlError: Logical file 'vrv' is not part of database 'vrv'. Use RESTORE FILELISTONLY to list the logical file names."

vrv is database name

What should I do to restore the file

score 17 · Accepted Answer · edited May 18 '12 at 13:53

Problem is here

sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFileLocation));

here databaseName means, name of the database specified in db backup file. But you are specifying the destination db name.

Change it to original db name

here the sample code to read db names from backup file

DataTable dtFileList = sqlRestore.ReadFileList(serverName);
string dbLogicalName = dtFileList.Rows[0][0].ToString();
string dbPhysicalName = dtFileList.Rows[0][1].ToString();
string logLogicalName = dtFileList.Rows[1][0].ToString();
string logPhysicalName = dtFileList.Rows[1][1].ToString

You are awesome thank you. I have battled and googled all day and this is the first time someone specified the true meaning of databasename. - So the follow-up question: How do I change the Logical Name after the restore happens? — Colema18, Jul 24 '15 at 18:10

score 0 · Answer 2 · answered Jun 23 '14 at 11:24

Now Go to "Browse" Tab and browse the following path-

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies OR C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies

Now Select the following dlls

Microsoft.SqlServer.ConnectionInfo.dll

Microsoft.SqlServer.Management.Sdk.Sfc.dll

Microsoft.SqlServer.Smo.dll

Microsoft.SqlServer.SmoExtended.dll

Microsoft.SqlServer.SqlEnum.dll

         public void  RestoreDatabase(String databaseName, String filePath, 
        String serverName, String userName, String password, 
        String dataFilePath, String logFilePath)
        {
            Restore sqlRestore = new Restore();
            BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
            sqlRestore.Devices.Add(deviceItem);
            sqlRestore.Database = databaseName;
            ServerConnection connection = new ServerConnection(serverName, userName, password);
            Server sqlServer = new Server(connection);
            Database db = sqlServer.Databases[databaseName];
            sqlRestore.Action = RestoreActionType.Database;
            String dataFileLocation = dataFilePath;
            String logFileLocation = logFilePath; 
            db = sqlServer.Databases[databaseName];
            sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
            sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFileLocation));
            sqlRestore.ReplaceDatabase = true;
            sqlRestore.Complete +=new ServerMessageEventHandler(sqlRestore_Complete);
            sqlRestore.SqlRestore(sqlServer);
            db = sqlServer.Databases[databaseName];
            db.SetOnline();
            sqlServer.Refresh();
      }

how to restore using restore class of Microsoft.SqlServer.Management.Smo namespace

2 Answers2

Linked