Some improvements to the database class

[zippy1981]

Mark,

I was thinking if there is some way to associate an MDF with a particular database, then the database class could have a static method that took a bunch of MDFs and returned an IList of database objects.

I presume BootPage.DBID is unique per instance of SqlServer.exe since its only a short. What is FileHeaderPage.BindingId?

It would certainly be nice to be able to throw a bunch of MDFs from a dead server at OrcaMDF and let it figure them out.

Naturally, having the OMS gui handle multiple DBs would be a next step, but even being able to make a simple report of that infro with powershell would be great.

[improvedk]

I'm not sure I'd like this to be in OrcaMDF directly. There's already plenty of complexity in just parsing the database files, and I think it's outside the scope of OrcaMDF to juggle multiple files and discover what belongs to what.

I've considered exposing a FileHeader parsing function, possible through a static, so another class might do the juggling around and then pass the results to OrcaMDF.

Basically I'm seeing it this way:

OrcaMDF.Core => Core parsing of a single database OrcaMDF.OMS => GUI OrcaMDF.Tools (Future project) => Various utilities and handlers that uses the core)

Not sure on what the BindingId is, but I believe it may be unique per database, per creation (as in, restore from backup => new BindingId).

[zippy1981]

So you don't know for sure what BindingId is? I presume since your code is better documentation of the MDF format than the Internals books available, the next step is to ask Paul Randal on twitter.

If your hypothesis is correct, that it is regenerated per backup restore, its perfect for this purpose though. I'll write a helper class that does what I want for now, and post it in a tools solution.

[improvedk]

Sounds good, and from the sound of Pauls comment, it would seem BindingId should be just what you're looking for - especially so if you can confirm it's recreated on attach/restore. If not, you'll have to identify on (BindingId, Name).

[zippy1981]

I ran this SQL, loaded both databases in OrcaMDF, and set a breakpoint to look at the binding id. The BindingId matched within a database but were different within the restored copy:

CREATE DATABASE Test_1  ON PRIMARY 
    ( NAME = N'Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2K8\MSSQL\DATA\test_1.mdf'),
    ( NAME = N'Test_a', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2K8\MSSQL\DATA\test_1a.mdf')
    LOG ON ( NAME = N'Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2K8\MSSQL\DATA\test_1.ldf' );
GO

BACKUP DATABASE Test_1 TO DISK = 'Test_1.BAK' WITH FORMAT;
GO

RESTORE FILELISTONLY FROM DISK = 'Test_1.BAK'

RESTORE DATABASE Test_2 FROM DISK = 'Test_1.BAK'
    WITH
        MOVE 'Test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2K8\MSSQL\DATA\test_2.mdf',
        MOVE 'Test_a' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2K8\MSSQL\DATA\test_2a.mdf',

        MOVE 'Test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2K8\MSSQL\DATA\test_2.ldf';

ALTER DATABASE Test_1 SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE Test_2 SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

Do you think the Database constructor should make sure all the binding ids match and that a BindingId property should be exposed?

[improvedk]

Awesome, sounds like the initial theory holds and BindingId is unique per database, per creation, and as such, an excellent binding value.

Having the constructor verify all files match sounds like a good idea. If they don't match, I suggest throwing a custom exception indicating the issue. I don't mind exposing the BindingId, like we probably should generally expose DB header values through the Database class itself.