SQL Server Version Build Numbers

Microsoft has churned out many different release levels of SQL Server, which makes standardizing your database infrastructure a tough job. In fact, most organizations wind up running many different versions of SQL Server. This has several different implications when it comes to database compatibility. T-SQL statements that work on one release might not work on an older release.

In other cases you might be running CTP versions on systems where you might not intend to. Perhaps more importantly, database backups that are done on newer releases often can’t be restored to older releases—sometimes even if they are the same major release number. For instance, I was recently surprised to find that a backup of one of my databases from a SQL Server 2008 R2 release couldn’t be restored to one of my SQL Server 2008 instances. To transfer the database, I wound up using the SQL Server Management Studio (SSMS) Object Explorer’s Tasks menu, selecting the Generate Scripts option, and walking my way through the Generate and Publish Scripts wizard.

Each different release of SQL Server has its own build number—for example, SQL Server 2008 R2 RTM has build number 100.50.1600.1. Knowing the different SQL Server build numbers is an important piece of management information.

To find the build number of your SQL Server instance run SSMS. The version number is listed immediately following the system name in the Object Browser. You can also find the SQL Server build number, the product’s production level, and edition name by opening Query Editor, then running the following T-SQL query:

SELECTSERVERPROPERTY('productversion'),SERVERPROPERTY ('productlevel'),SERVERPROPERTY ('edition')

Below are the recent version numbers for the new SQL Server Denali CTP1, SQL Server 2008 R2, SQL Server 2008 as well as the previous production releases of SQL Server and its major service packs back to SQL Server 2000.