asked on

Restore database using SMO to a different server

I am not able to Restore a DB to a different server from a backup using SMO. I have attached the code below.
Getting the following exception,
System.Data.SqlClient.SqlError: Logical file is not part of database 'testDB'. Use RESTORE FILELISTONLY to list the logical file names.
I dont know how to use RESTORE FILELISTONLY in SMO.
Your immediate help is highly appreciated.

Server svr = new Server(cmbDtServer.SelectedItem.ToString());
            Restore res = new Restore();           
            res.Database = tbDBName.Text.ToString();            
            res.Action = RestoreActionType.Database;
            res.Devices.AddDevice(dir.ToString()+"\\"+ cmbFileList.SelectedItem.ToString(), DeviceType.File);
            res.ReplaceDatabase = true;
            String dataFileLocation = tbDataPath.Text + tbDBName.Text + ".mdf";
            String logFileLocation = tbLogPath.Text + tbDBName.Text + "_Log.ldf";        
            res.RelocateFiles.Add(new RelocateFile(tbDBName.Text.ToString(), dataFileLocation));
            res.RelocateFiles.Add(new RelocateFile(tbDBName.Text.ToString(), logFileLocation));
            res.SqlRestore(svr);

Open in new window

ASKER CERTIFIED SOLUTION

DBAduck - Ben Miller

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

asadeen

ASKER

I am gettig the following exception, when I try to restore the backup from some other server,
{"System.Data.SqlClient.SqlError: The path 'F:\\USER_LOGS\\\\ftcat_documentindex' has invalid attributes. It needs to be a directory. It must not be hidden, read-only, or on a removable drive."}

When I try to restore the local backup of that server I am able to do.

Thanks in advance

DBAduck - Ben Miller

The major thing about this is that when you restore databases from another server, you must remember that the SQL Server service credentials are always the ones used to restore or backup. This means that the account that SQL Server Service starts under must have access to that file.

You also need to ensure that the path to the file that you are restoring from needs to be in UNC path format if it is on another server "\\servername\share\filename.bak"

So if your SQL Server is running under Local System, or Network Service or a local account, you will not be able to do it because they do not reach past the boundaries of the server. But if it is a domain account then it has the ability to do so if you add that user or group to the security of the other server you are trying to restore from.

asadeen

ASKER

In my case the BAK file is in the local hard drive of the same SQL box that I am trying to restore, The SQL service is running under local system account, I am logged in that box. The .bak file was copied to the local harddrive from a different SQL server. The bottom line is, everything is running local, no UNC path reference.

asadeen

ASKER

I just noticed that ftcat_documentindex is the name of the full text search index used by the CRM, and is creatign a problem when I restore.

asadeen

ASKER

well that was fixed. thanks dbaduck for your help.

asadeen

ASKER

The final code looks like this

            Server svr = new Server(cmbDtServer.SelectedItem.ToString());
            Restore res = new Restore();
            res.Database = tbDBName.Text.ToString();
            res.Action = RestoreActionType.Database;
            res.Devices.AddDevice(dir.ToString() + "\\" + cmbFileList.SelectedItem.ToString(), DeviceType.File);
            res.ReplaceDatabase = true;
            String dataFileLocation = tbDataPath.Text + tbDBName.Text + ".mdf";
            String logFileLocation = tbLogPath.Text + tbDBName.Text + "_Log.ldf";
            DataTable dt = res.ReadFileList(svr);
            string LogicalDBName = dt.Rows[0]["LogicalName"].ToString();
            string LogicalLogName = dt.Rows[1]["LogicalName"].ToString();
            RelocateFile reloData = new RelocateFile(LogicalDBName, dataFileLocation);
            RelocateFile reloLog = new RelocateFile(LogicalLogName, logFileLocation);
            res.RelocateFiles.Add(reloData);
            res.RelocateFiles.Add(reloLog);
            res.SqlRestore(svr);

Open in new window