Read a backup file to find the version

  • 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?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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();

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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.

  • 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")

  • 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.

    [font="Arial"]---

    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]

    How to ask for help .. Read Best Practices here[/url].

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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?

  • sure: here's the same databases as a new backup, but placed on a disk with sector sizes of 512:

    hope this helps!

    CodeTestDBs-512.zip

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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