Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
1  
Regarding the last question, you're right. I forgot to show the elephant in the room: why would you make your own script as long as there are some great scripts out there (like Ola Hallengren's script )?. If it's for learning and playing, yeah, sure do it, but if it's production use, at least give that one a try. –  Marian Nov 23 '12 at 14:45
    
Many thanks for a good and clear answer. Q3) was really just asking why I didn't get an error if headeronly returned uint64 and the column in my table was type bigint. But I do believe that's now been answered :) With regards to my current scenario - I've been asked to write scripts to automate (with SSMS Job Agent) our back-up process. This involves taking .baks from the DBs on our dev server here and storing & restoring them to a second machine. I included the compression columns as I was just trying to mimic the table given in the resultset. This is still one of my first ventures in SQL. –  cprlkleg Nov 23 '12 at 15:11
    
No problem and best of luck! Definitely check out the internet for some scripts and check out that Ola Hallengren script Marian put in the comment a couple above this one. Less wheel reinventing for you. Have a great day. –  Mike Walsh Nov 23 '12 at 15:15
add comment

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?

share|improve this answer
    
And I thought I mentioned in my answer below but I only hinted at it. I'm with Marian - I believe this is a likely documentation error. I'll raise that via Connect or on the MVP discussion list to see if I'm wrong here. Looks like the uin64 was changed to BIGINT in SQL Server 2008R2 as I said below, so it looks like just the BYTE(1) remains as a potential issue. –  Mike Walsh Nov 23 '12 at 14:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.