Update:

After all of this SMO stuff failed (changing the permission set to unsafe got around the security exception, but instead I got an error about functionality being disabled in the SQLCLR) I gave up and turned back to the idea of opening a connection and using the engine to issue the restore headeronly command.

I used a context connection to avoid having to pass connection information to the function.

The code looks like this:

public static System.Collections.IEnumerable headers(System.Data.SqlTypes.SqlString full_backup_path) {
    using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection("context connection=true"))
    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("restore headeronly from disk = '" + full_backup_path.ToString() + "'", connection))
    using (System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd)) {
        System.Data.DataTable dt = new System.Data.DataTable();
        da.Fill(dt);
        return dt.Rows;
    }

Unfortunately this also fails:

System.Data.SqlClient.SqlException: Invalid use of a side-effecting operator 'RESTORE VOLUME' within a function

Getting backup file details from a UDF?

I've been writing a set of file copy and restore stored procedures to work with Ola Hallengren's check, optimize and backup routines.

As part of the restore side, I needed a way to inspect the backup headers and contents from within TSQL. The simple way to do this is to create a (temp) table (variable), and use insert ... exec('...') syntax.

I used this method at first, but I didn't like it very much, for the same reason I never like it: it's ugly, and it makes your code ugly. In this instance, it meant declaring a table with 51 columns (to match the output of restore headeronly), then a separate statement to build the restore command string, then another to insert the execution of the string into the table, before finally getting to the desired point of actually using this information.

In fact, the number of lines of code required for this is about half of the total length of the stored procedure.

This was simply not good enough, so I started thinking of other ways to get the info. The most elegant solution, in my opinion, would be a table valued UDF. But how would I get the output of the restore headeronly command from within a UDF? Executing dynamic sql is not supported, and there are no extended procedures (that I know of) to do the same task.

SQLCLR seemed like an obvious solution.

Table Valued UDFs in SQLCLR

I'ved used SQLCLR before for a few things. A bit of string parsing, and some routines I created to go into a file_system schema. I was originally planning to use these to do the file handling operations as part of this project. Eventually I decided to xp_cmdshell a robocopy. It was quicker, cleaner, clearer, and far, far easier.

Maybe I should have taken this as a sign.

The first thing to note about writing table valued functions in SQLCLR is that there's going to be a LOT of typing to do if your function returns a lot of columns. For each column in your result set, you'll need to:

• Include the (SQL datatype based) definition of the column as part of a TableDefinition attribute for your .net function
• Create an appropriate (CLR Microsoft.SQLServer.Types based) output parameter on a second function (which SQL Server uses to actually retrieve a row from the collection returned by the first function)
• Assign a value from your incoming (probably untyped) collection to one of those output parameters (this means a cast is required).
• include the column definition as part of the TSQL function declaration for the returned table.

If I expected to be writing a lot of table valued SQLCLR functions there's no way I'd continue to do this manually. There is a heck of a lot of typing and a heck of a lot of opportunity for error (eg, misspelling or mismatching a data type or column/variable name somewhere. With 51 columns you have 408 opportunities for error). I would make a mapping table in sql between the SQLCLR types and TSQL types and generate the code automatically.

Next, I'd need a way to actually get the backup contents from the file from within .NET. Passing the instance name from which the function was called back in to the function to be used to execute a sql command containing the text for the restore headeronly statement was one idea, but again, it seemed silly. Instead I decided to use SMO.

This cost me a bit of time.

Using SMO in SQLCLR

First I had to download and install SMO. I'd be developing on a 32 bit machine, so I needed to download the appropriate installer. The SQL Server is on a 64 bit OS, however, so it would need the 64 bit installer.

Next I obviously had to add a reference to Microsoft.SQLServer.SMO to my project. But I couldn't get this from the list of available .NET assemblies. I had to browse to the dlls sitting in the assemblies folder under my local SQL Server path.

Less obviously, I also had to add a reference to Microsoft.SqlServer.Management.Sdk.Sfc, Microsoft.SqlServer.ConnectionInfo, and Microsoft.SqlServer.SmoExtended. I didn't know this until I tried to compile. The first two I still do not understand. The third is needed because the Restore object, which is in the Microsoft.SQLServer.Management.SMO namespace, is not in fact in the SMO assembly, but rather the SMOExtended assembly. But not the SMOExtended namespace. I don't think such a namespace exists. Of course! In fact, I just realised... I might not need the reference to SMO itself after all.... eh, nevermind that for now...

I compiled my assembly, moved the DLL from my release folder to somewhere the server could see it, and then tried to create the assembly. This failed. Apparently my references were not available to the database. OK, I tried to register SMO. I was told no. This is a system assembly. Try wrapping it in your own dll, it told me.

This was confusing.

OK, so after some googling it turns out this error means that the assembly is already registered with SQL. Odd, I didn't see it in the list of "pre-registered" assemblies. Perhaps the list was for 2005. I'm on 2008R2.

Ah, but SMOExtended was not registered. I found this out not through some other easy to find list of "dll's that you think might be registered, but aren't." Rather, because trying to register it was successful.

I didn't need to register the two mystery DLLs. But OK, success! I had created my assembly in .NET and SQL Server

Creating the Function

This part is relatively easy. I say relatively, because it is at this point that you will discover whether or not you have accidentally mapped a tinyint (such as CompatabilityLevel returned by restore headeronly) to a SqlInt16, or SqlBinary, or SqlB...something, instead of a SqlByte. If you have, you get to go back and edit your .NET code, then recompile, recopy, and recreate (or alter) the assembly.

By the way, if you DO make the kind of change that requires you to drop and recreate the assembly (changing data type mappings fortunately isn't one of these) you'll first need to drop all of the objects referencing the assembly. This might lead you to think that one SQL object per DLL is the safest way to do things. Then again, how many dll's do you want, and why would SQL Server split the operation into two operations, "create assembly" and "create (object)", if the only sensible thing is to have one object per assembly?

You'll also have to do this multiple times if you have more than one error, since it will only tell you about one erroneous column mapping per create function attempt.

Finally the hard part: Namespacing. At my company all code is created in a "CompanyName" namespace. Under this, I had decided to use a "SQLCLR" namespace before, and continued to do so here. For this particular project I decided I'd use a "DBA" namespace under that. Finally, I had a class called "BackupFile". I might want to do more things with these later. (In fact I know I do, since I am planning on going through all of this again for the restore filelistonly command).

Now, I had created the assembly itself in SQL as "CompanyName.SQLCLR.DBA". But I could have called it anything I wanted. The name of the assembly as it exists in SQL Server need not have anything to do with the namespaces or objects within the file. Naming it this way seemed sensible to me. It's a question of balancing out the number of functions in your assembly (more means longer compile and more likelihood that you will not be able to use an alter assembly, but rather will have to drop and recreate later, to change the assembly in SQL, depending on what changes you make in .NET) and the number of assemblies (ie, individual dlls in source control) you need to register and maintain. Having the name reflect the contents seems to help maintainability.

The function itself I named "headers". That is to say, in .NET, the full path would be CompanyName.SQLCLR.DBA.BackupFile.headers.

This combination, which seems reasonable to me, means the external name of the function, as far as the create function syntax is concerned, is:

"...as external name [CompanyName.SQLCLR.DBA].[CompanyName.SQLCLR.DBA.BackupFile].headers"

This seems a bit ridiculous. Oh well. I created the function in the "DBA" namespace in my library database, calling it "DBA.backup_file_headers". Right, time for a test-toast!

A .NET Framework error occurred during execution of user-defined routine or aggregate "backup_file_headers":
System.Security.SecurityException: That assembly does not allow partially trusted callers.

Bugger.

Code!

You can find the backup file headers .NET code here

The related SQL code is here