C# code to restore Database from remote share drive location using SMO

Question

Hey

I am using the below code to restore databse

it works fine if the shared drive is in same server but when i try for remote server its not working

giving the following err: 

{"System.Data.SqlClient.SqlError: The media family on device '\\\\servername\\v\\Test.bak' is incorrectly formed. SQL Server cannot process this media family."}

       //Restore Database QA Server

                   

            Database dba = s.Databases["Test"];

                   Restore res = new Restore();

                   res.Devices.AddDevice(@"\\servername\v\Test.bak", DeviceType.File);

                   // Set the backup device from which we want to restore, to a file

                   BackupDeviceItem bkpDevice = new BackupDeviceItem(@"\\servername\v\Test.bak", DeviceType.File);

                   res.Database = "Test";

                   res.Action = RestoreActionType.Database;

                   res.RelocateFiles.Add(new RelocateFile("Test", @"J:\MSSQL$BACKUPTEST\Data\Test_Data.mdf"));

                   res.RelocateFiles.Add(new RelocateFile("Test_Log", @"J:\MSSQL$BACKUPTEST\Data\Test_Data.ldf"));

                   res.PercentCompleteNotification = 10;

                   res.ReplaceDatabase = true;

                   res.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);

                   res.SqlRestore(s);

                   writer.Flush();

                   writer.Close();

        }

        static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)

        {

            Console.WriteLine(e.Percent.ToString() + "Restored");

        }

Answer 1

Are you connecting to the server through SQL Server authentication or Integrated Authentication ? If the latter, do you have access to the share and the rights (NTFS and sharing) to read the file ? If the first, does the SQL Server sservice account have the priviledges to do this ?

-Jens

---

Jens K. Suessmeyer http://blogs.msdn.com/Jenss

Answer 2

No Permission issue....i have all permission i am able to do remote backup on the same shared drive 

i am using windows authentication ......... 

if try on the same server where the share drive is i am able to do it .... is not working for remote server 

i am actually developing a small application where restore is one step which is not working in network i be will running the application from some X server which has to go to A server and backup on a share drive (which is working fine) then from the sharedrive it has has to restore this database to server B (which is not working in network) i am using the above code and trying it i got struc here ....can u pleas help me out ..i need to complete asap any thing wrong in code please update and send me....... 

Thanks.....

Answer 3

One of the possiblities is that your backup is corrupt, or it doesn't contain logical filenames as "test" or "test_log"

Try executing RESTROE HEADERONLY on that file, and post the result please, i'll be able to help you better

Regards
Kunal

---

Kunal Chourasia

Answer 4

hey.. thanks for the reply

i am able to get it with below code

                   Restore res = new Restore();

                   res.Devices.AddDevice(@"\\hqsqlbkup1\VN\Atlas.bak", DeviceType.File);

                   BackupDeviceItem bkpDevice = new BackupDeviceItem(@"\\hqsqlbkup1\VN\Atlas.bak", DeviceType.File);

                   res.Database = "Atlas";

                   res.Action = RestoreActionType.Database;

                   res.RelocateFiles.Add(new RelocateFile("Atlas_data", @"J:\MSSQL$BACKUPTEST\Data\Atlas.mdf"));

                   res.RelocateFiles.Add(new RelocateFile("Atlas_log", @"J:\MSSQL$BACKUPTEST\Data\Atlas_log.ldf"));

                   res.PercentCompleteNotification = 10;

                   res.ReplaceDatabase = true;

                   res.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);

                   res.SqlRestore(s);

                   writer.Flush();

                   writer.Close();

it works fine now ....i does not work when trying to restore mdf and ldf files on network as network device not supported for database files.

(\\idc1vsql1001\\J$\MSSQL$BACKUPTEST\Data\Atlas.mdf)

i have an other urgent issue ..can u guys pls look into it..

http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/85d3a5c4-d04f-445e-9bf8-6c7f044ced02

Answer 5

First you need to map the network drive,on the server where you wnat to restore the database(through NET USE Command).Then cross check if the drive is mapped or not in MY COMPUTER.

Then you will be able to restore the database from the network drive.

For NET USE command see the below links.

http://www.cezeo.com/tips-and-tricks/net-use-command/

http://pcsupport.about.com/od/commandlinereference/p/net-use-command.htm

---

Please click "Propose As Answer" if a post solves your problem. OR "Vote As Helpful" if a post has been useful to you. Thanks, Debasish Mohanty Blog:http://sqlcodebank.blogspot.com

Answer 6

Hello,

It is the same problem with a CREATE DATABASE. I would suggest you to have a look at :

http://msdn.microsoft.com/en-us/library/ms176061(SQL.100).aspx

Search the part FileName ( around 25% of the page ), for os_file_name ,you will see

"Is the path and file name used by the operating system when you create the file. The file must reside on one of the following devices: the local server on which SQL Server is installed, a Storage Area Network [SAN], or an iSCSI-based network. The specified path must exist before executing the CREATE DATABASE statement" and , more important, for you  in the part Remarks ( around 50% of the page ):

" By default, using network database files (stored on a networked server or network-attached storage) is not enabled for SQL Server. However, you can create a database that has network-based database files by using trace flag 1807. For information about this trace flag and important performance and maintenance considerations, see this http://support.microsoft.com/kb/304261"

But , i don't know how to execute a T-SQL statement under a trace flag 1807

It seems that it is possible to put your data/log files on a network thru T-SQL statements, but with "difficulties". A restore is using the same rules than the CREATE DATABASE T-SQL statements, it is why i gave you only links related to T-SQL.

For the moment, i would give you an advice : try on your configuration with T-SQL statements, if it is working, you have a possibility to hope a solution to your problem. If it is not working, SMO will not help you as every SMO "command" is translated in Transact-SQL statements ( there is a class for that : SqlCaptured which you will find in the Microsoft.SqlServer.Management.Common

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.capturedsql(SQL.100).aspx

Don't hesitate to post again for more help or explanations ( especially because i know i have a really poor english )

Have a nice day 

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 7

Check out this link.

http://www.eggheadcafe.com/community/csharp/2/10308570/remote-sqlserver-database-backup-and-restore-on-local.aspx