Get the SQL Server version from a .bak file?

Question

My client's support team gets .bak files from their clients when there is a problem. We are currently about 1/2 in SQL Server 2005 and 1/2 in SQL Server 2000. *Before* we restore the file, we would like to know whether it is a SQL Server 2000 or SQL Server 2005 database. (Otherwise, if we fix any database errors and return it ... the client won't be able to use it if we accidentally upgraded the database from 2000 to 2005.)

 

I know I can get this from dbcc checkprimaryfile if I have the .mdf file. But in this case I only have the .bak file.

 

Is there anything (utilities, SMO commands, other?) that I can run against the .bak file to determine which version it is?

 

Thanks!

 

Answer 1

check RESTORE HEADERONLY  option . The result will give you following columns which says which is the version sql server  from which the backup was taken softwareVersionMajor, SoftwareVersionMinor, SoftwraeVersionBuilt

 

backup database testdb to disk='d:\testdb.bak'

 

RESTORE HEADERONLY

FROM DISK = N'd:\testdb.bak'

WITH NOUNLOAD;

 

Madhu

Answer 2

Actualy this won't work if you will try to run restore in e.g. SQL 2005 and the bak file will be from 2008. MS SQL is kind of dumb in recognizing it's own backups ;-P.

However this C function should rocognize SQL at least from 8 to 10 (that is from 2000 to 2008):

int getVersion(FILE *fBak)
{
	// get guess bajts ;-)
	int b1, b2;	// bajts
	fseek(fBak, 0x42L, SEEK_SET);
	b1 = fgetc(fBak);
	fseek(fBak, 0x434L, SEEK_SET);
	b2 = fgetc(fBak);
	// guessing...
	if (b1==0x01)
	{
		return 2000;
	}
	if (b2=='8')
	{
		return 2005;
	}
	if (b2=='7')
	{
		return 2008;
	}
	return -1;
}

SQL 2008 R2 is recognized as 2008.