2

I'm trying to write a C# application that restores many databases from .bak files placed in a certain folder. To do so I need the databases logical name from the files.

So I want to execute the following query : 

RESTORE FILELISTONLY FROM DISK = N'C:\Folder\File'

The problem is: I can't get the result of this to my application, when I execute it in SQL Server Management Studio, it shows the name I want.

I tried to use ExecuteReader function from SqlDataReader but it doesn't return any data.

Can someone help me figure out how to get the result of queries like restore database, backup database into a variable in a C# application ?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Soufiane.Ach
  • 63
  • 6
  • 1
    I am not sure, therefore this is not an answer, but could it be [this approach](https://stackoverflow.com/a/8689358/1132334), [example here](https://stackoverflow.com/a/1880507/1132334)? – Cee McSharpface Aug 04 '17 at 16:46
  • 2
    Show the code where you use ExecuteReader. – Joel Coehoorn Aug 04 '17 at 16:56
  • 1
    `RESTORE FILELISTONLY` returns a result set . You can read a result set with `ExecuteReader`. Nothing wrong in this part of the app. – Serg Aug 04 '17 at 16:57
  • I presume you meant to say that you are using the ExecuteReader method of SqlCommand class, which returns SqlDataReader? We really do need to see the code to help. – Jonathan Willcock Aug 04 '17 at 17:14
  • Thank you guys for all you answers i really appreciate it , i think that it's working now , i wasn't looping the Reader because the name i want is in first row but i guess i need to write this ==> While (Reader.Read()) regardless of the situation . thanks again Guys – Soufiane.Ach Aug 04 '17 at 21:13

1 Answers1

3

The command RESTORE FILELISTONLY does return a result set as per the documentation RESTORE Statement - FILELISTONLY. What I did find during testing my code, you need to make sure the user that is running the application has permissions to read the specified file and directory. Otherwise you will run into issues.

For a code example of this:

var command = @"RESTORE FILELISTONLY FROM DISK = N'F:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Scratch.bak'";

using (var sqlConnection = new SqlConnection("Server=localhost;Database=Scratch;Trusted_Connection=True;"))
using (var sqlCommand = new SqlCommand(command, sqlConnection))
{
    sqlConnection.Open();
    var sqlDataReader = sqlCommand.ExecuteReader();

    while (sqlDataReader.Read())
    {
        Console.WriteLine($"Logical Name: {sqlDataReader["LogicalName"]}");
        Console.WriteLine($"Physical Name: {sqlDataReader["PhysicalName"]}");
        Console.WriteLine($"Type: {sqlDataReader["Type"]}");

    }
}

You may also find that when trying to work SQL Server management, using the SqlConnection and SqlCommand objects may be more frustrating then they are worth. I strong recommend using the SQL Server Management Objects (SMO). This is actually what management studio uses when you are work with SQL Server. I have an example on GitHub if you are interested in it. Checkout GitHub: SMO Demo.

tj-cappelletti
  • 1,774
  • 12
  • 19