We help IT Professionals succeed at work.

Restore database using SMO to a different server

asadeen
asadeen asked
on
Medium Priority
1,586 Views
Last Modified: 2012-06-27
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

Comment
Watch Question

SQL Server Architect
CERTIFIED EXPERT
Commented:
In order to do RESTORE FILELISTONLY in SMO you need to do this: (see code)

The ReadFileList(server) will return a datatable that is really a RESTORE FILELISTONLY.

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);
            DataTable dt = res.ReadFileList(svr);

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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 MillerSQL Server Architect
CERTIFIED EXPERT

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

Author

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

Author

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

Author

Commented:
well that was fixed. thanks dbaduck for your help.

Author

Commented:
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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.