[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Restore database using SMO to a different server

Posted on 2009-02-18
7
Medium Priority
?
1,437 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

0
Comment
Question by:asadeen
  • 5
  • 2
7 Comments
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 2000 total points
ID: 23681152
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

0
 

Author Comment

by:asadeen
ID: 23684186
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
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 23686170
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.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:asadeen
ID: 23686313
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.
0
 

Author Comment

by:asadeen
ID: 23686548
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.
0
 

Author Comment

by:asadeen
ID: 23686759
well that was fixed. thanks dbaduck for your help.
0
 

Author Comment

by:asadeen
ID: 23692481
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

0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question