Post reply

Finding the version of sql server 2005 from backup files

  • July 17, 2012 at 2:48 am

    #277870

    Hi All,

    My question may be trivial, silly etc, but i just want to know it.

    is it possible to find out the build of sql server 2005 from the available database backup files.

    For example, in production i'm running a sql 2005 built during september 2009 and my staging server hoists a sql 2005 built during october 2005.

    Is it possible to restore the server backup (sql 2005 build september 2009) to staging server (sql 2005 build october 2005).

    Thanks for reading the question with patience 🙂

    Sreenivasan

  • July 17, 2012 at 6:32 am

    #1513550

    yes it's possible. SQl lets you restore previous versions of SLQ server, up to three versions behind, i think?( so SQL 2008 can restore SQL 7 or SQL 2000 or 2005 , SQL R2 can restore 2000,2005 and 2008)

    the RESTORE HEADER ONLY gives some results that include a column named [DatabaseVersion]

    /*--Partial Results

    BackupName BackupDescription BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion

    ----------------------------------- ------------------ ---------- ----------------------- ---------- -------- ---------- --------- ---------- ------------- ---------------

    PERFECT1000W-Full Database Backup NULL 1 NULL 0 1 2 sa DBSQL2K5 PERFECT1000W 611

    */

    RESTORE HEADERONLY

    FROM DISK = N'C:\data\Backups\PERFECT1000W_10142011.BAK'

    WITH NOUNLOAD;

    GO

    jonathan kehayias has an older post that lists the mapping of numbers to versions here:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/28/database-version-vs-database-compatibility-level.aspx

    •SQL Server 7.0 databases have version number 515

    •SQL Server 2000 databases have version number 539

    •SQL Server 2005 databases have version number 611/612

    •SQL Server 2008 databases have version number 655

    so based on the backup, you can see what verison it might belong to.

    does that help?

    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!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply