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?
EDIT: SQL Server 2008