Invalid data types in restore headeronly resultset

Question

I'm trying to write a SQL Server script to iterate through .bak files in a directory and then restore them to our server. In doing so, I've created three temp tables: #Files to keep track of the file-list returned by running xp_dirtree, #HeaderInfo to hold data returned when querying restore headeronly to get the database names and #FileListInfo to hold data returned from querying restore filelistonly to get the logical file names.

My question is regarding the #HeaderInfo table. Consulting the MSDN definition of the resultset returned from restore header only, I find that the fifth column (Compressed) and the last column (CompressedBackupSize) have 'invalid data types' (BYTE(1) and uint64, respectively). This, obviously, gives me an error when I try to execute the query. To get around this, I have used tinyint and bigint, respectively, and the code now runs fine.

My question(s) is/are this/these:

• Is using tinyint/bigint the 'correct' work around for this? Or is there a better way to do it?
• Is using them likely to cause any undesired behaviour?
• If SQL is expecting BYTE(1) and uint64s, why does using different data types not cause an error?
• And why does MSDN specify BYTE(1) and uint64 if they're not what gets returned? What are these used for and where?
• Bonus question, for anyone who's interested, is there a more elegant/efficient way of automating a restore script?

Many thanks

EDIT: SQL Server 2008

Answer 1

First your first questions

1. I would use tinyint for the BYTE(1) in this case they told us the possible values are 1 or 0. BIT may also work. You could also try BIT. But uint64 is an unsigned 64 Byte integer. BIGINT is signed, so the max value is lower. So technically speaking a DECIMAL(20,0) or greater precision would be used here. But in later versions of that same article this is a BIGINT (For SQL Server 2008 R2 and SQL Server 2012) so I am sure you are fine with BIGINT here. If you get enough disk space and time to create a database big enough to compress to a value that blows BIGINT you can test this theory out someday ;-)
2. No undesired behavior if you go with SMALLINT/BIGINT/DECIMAL(20,0)
3. I am not sure I understand your question, but I believe the answer is conversion if you are asking what I think you are asking but this is potentially just an oops
4. I'm not sure why those datatypes are in the documentation but you've chosen good logical approximations.

Then the last question

I hate to shove off on this one, but I'm kind of going to do that. There are a lot of great restore scripts out there on the internet for different scenarios. You haven't fully described yours so not sure I can comment on the efficiency/elegance but you are right to read the headers to determine what you do next. Some questions to ask yourself:

Are you looking at things like the date to ensure you restore the latest? Are you looking at things like full/diff/log backups and accounting for them in the restore? What purpose is this for? Restoring a dev environment? Or for a production restore? If a dev restore, I like to go more automated. If a prod restore I like to have a script that eliminates some "oops" factor from a critical production restore but not automate so much of it that it makes it easy to forget to do a critical step or do something like backup the tail of the log. I'd search for restore scripts and see what others have done, ask yourself these questions and incorporate what you like.

I also am not sure you need to know if the file is compressed or what the compressed size is. Those facts shouldn't be terribly necessary for a restore script since SQL just handles the restore of a compressed backup for you. You don't have to tell SQL it is compressed. So you may just drop those columns altogether and only take what you require from the header to perform you restore.

Answer 2

You can find the data types mapping between SQL Server and C# on MSDN. I believe that the two return types there are just a mistype in the documentation, and not the actual requirement.

Actually Uint64 should be BIGINT, as you already found, nothing else matches closely.

While BYTE(1) is equivalent to BINARY in SQL Server, not tinyint. But going through the same documentation I'd say that this one should actually be a bit data type, as the returned values are only 1 and 0.

Except for unnecessary casts, there shouldn't be any issues in the future.

Bonus answer: this can be done also in powershell scripts, SSIS packages, whatever your preference is. Do you verify anywhere the type of the backups and their order of restore?