March 6, 2013 at 12:57 pm
Is it possible to read a backup file directly and determine the version of the server that created it? I've been Googling for it for a while but haven't been able to find anything. I did find a utility (SQL BAK) that can do it but I need to include this capability in my app. Anyone know how to read a backup file?
March 6, 2013 at 1:37 pm
No need to read the backup file manually.
RESTORE HEADERONLY FROM DISK = <path to backup file>
Among the columns are SoftwareVersionMajor, SoftwareVersionMinor and SoftwareVersionBuild that tell you the version and service pack of the server that the backup was taken from.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2013 at 1:57 pm
GilaMonster (3/6/2013)
No need to read the backup file manually.
Yes there is. The system they are installing on may not yet have SQL Server installed on it. I will need to know what version of SQL the backup came from in order to validate what I will let them install.
March 6, 2013 at 2:07 pm
Copy the backup to a machine that has SQL server (or to a machine that a SQL server instance can see across the network) and run a restore headeronly. Even a SQL express instance on a laptop is good enough.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2013 at 2:25 pm
OK, so one of my coworkers opened a backup file from a 2005 server and a 2008 in a HEX editor server and found where there was a difference in the header of the file and it turns out that at position 3756 (0x0EAC) there is a two byte value that when converted to an integer is the internal database version. Here's some C# code to get it:
FileStream f = new FileStream("C:\\SQLData\\MyBackupFile.bak", FileMode.Open);
byte[] b = new byte[2];
f.Seek(3756, SeekOrigin.Begin);
b[0] = (byte)f.ReadByte();
b[1] = (byte)f.ReadByte();
Int16 dbVersion = BitConverter.ToInt16(b, 0);
f.Close();
March 6, 2013 at 3:14 pm
tried your code, and it doesn't work on all situations;
it found SQL2005 as version 611 just fine, but only that for me.
For others(i have all on my dev machine), it returned:
zero for a SQL2008 database with version 655,
zero for 2008R2 database with version 661
zero for 2012 database with version 706
i didn't have a 2000 database backup to test with.
Lowell
March 6, 2013 at 3:39 pm
there might be something in the header that might be causing that to shift position. In my case the database name being backed up was always the same. So, a different database name might shift where that value it located. I'll do a little more checking.
FYI, I had tested it on a SQL 2008 R2 backup file and 2005 backup
March 6, 2013 at 4:43 pm
I tested this against various database backups from SQL 2000, 2005, and 2008 and it worked as advertised. There were backups taken from different servers and with different database names. It would not make much sense that this header info has changed from version to version since that would just involve more coding to determine the version when restoring a backup. Not sure why it would return such erroneous results for you.
March 6, 2013 at 4:47 pm
Also, if you would like to use this in PowerShell just execute the following to create a new object type
Add-Type -TypeDefinition @"
using System;
using System.IO;
public class GetSQLBackup
{
public static int getDbVersion(string backupFilePath)
{
FileStream f = new FileStream(backupFilePath, FileMode.Open);
byte[] b = new byte[2];
f.Seek(3756, SeekOrigin.Begin);
b[0] = (byte)f.ReadByte();
b[1] = (byte)f.ReadByte();
Int16 dbVersion = BitConverter.ToInt16(b, 0);
f.Close();
return dbVersion;
}
}
"@
After executing this you can call it like this:
[GetSQLBackup]::getDbVersion("C:\\SQLBackups\\YourBackupFile.bak")
March 6, 2013 at 5:22 pm
Gail's approach would be the recommend way, instead of you trying to reverse engineer it. SQL Express doesn't cost anything and you can quickly find out, instead of putting all this effort in for reverse engineering. IMHO.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 7, 2013 at 6:38 am
Lowell, I'd like to find out more about why it failed on your backups since I tested it on several versions and different database names. Can you give me the backup command that you used to create the backups or if it's possible can you give me some sample backup files that it failed on?
I'd also like to get some others to try this to see if it works for them.
March 7, 2013 at 7:03 am
easy peasy!
The error could be on my side, or maybe something is different on my backups that you should double check against.
!edit!
something i was thinking: my 2008/2008R2/2012 backups are on a disk that is formatted with 4k sectors instead of the "normal" 512 sectors! that might be the difference!
for my 2008R2 for example, i created a brand new database CodeTest, and did a normal backup via the GUI:
the scripted command is:
BACKUP DATABASE [CodeTest] TO DISK = N'F:\SQLData\SQL2008R2\Backup\CodeTest.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'CodeTest-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
i did exactly that code in all 4 version i have available to me.
here is a link to a zip file with all four empty databases, each one created on one of my servers or local instances:
so you can test it on your side:
CodeTest2005.bak
CodeTest2008.bak
CodeTest2008R2.bak
CodeTest2012.bak
Four SQL Backups CodeTestDBs.zip 729kb zip/6 meg uncompressed
Lowell
March 7, 2013 at 9:39 am
Well Lowell, sure enough your backups fail (i.e. return zero for version number), although backing up my database using your command works fine. I don't have a 4K sector disk to test on right now so I can't verify that that is the issue. It would seem odd if that were the case as it would make the file usability dependent on the disk. Do you have a "normal" 512 byte disk that you could backup on?
March 7, 2013 at 9:46 am
sure: here's the same databases as a new backup, but placed on a disk with sector sizes of 512:
hope this helps!
Lowell
March 7, 2013 at 10:05 am
The sector size must be the difference, the new files all report the correct version. I looked through the 4k files and found that the offset is at 10412 (so you could modify the code I posted to set the Seek offset to 10412 and it will work on the 4k files). I wonder if there is a way to determine if a file was created on a 4k sector disk?
Thanks for your time in helping me find out more about this. I really appreciate it.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply