2008 R2 Compatibility

It is not possible to restore a SQL Server 2008 R2 database on SQL Server 2008 due to the physical version numbers being incompatabile. You can run DBCC DBINFO to read the database header page to collect the actual information:

DBCC TRACEON (3604);
GO
DBCC DBINFO
GO
DBCC TRACEOFF (3604);

dbi_version = 661

A summary of versions

* 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
* SQL Server 2008 R2 databases have version number 660

An excellant post The Rambling DBA: Jonathan Kehayias
and Paul Randal
explain this.

SQLBits

The next SQLBits conference, SQLBits 7: The Seven Wonders of SQL, will be taking place from September 30th to October 2nd at York University.

Database Management Methods

I am currently researching what methodologies, tools or processes are currently used in the day to day management of SQL Servers.

Can you help with my survey on Database Management Method on bit.ly/aMEqbW

Thank you helping with the research

SQL Server 2008 R2

SQL Server 2008 R2 was released to Released to Manufacturing on 21 April 2010.

The official press release summarizes the main benefits as:
1. Managed self-service business intelligence (BI) for reporting and analysis
2. Enterprise-class scalability and greater IT efficiency
3. Platform integration spanning the data centre to the cloud

SQL Server Codenames

I was interested to see what all the SQL codenames were

Sphinx - SQL Server 7.0
Plato - SQL Server 7.0 OLAP Services
Shiloh - SQL Server 2000
Liberty - SQL Server 2000 (64-bit)
Rosetta - SQL Server Reporting Services
Yukon - SQL Server 2005
Katmai - SQL Server 2008
Kilimanjaro - SQL Server 2008 R2
Madison - Microsoft SQL Server 2008 R2 Parallel Data Warehouse
Bulldog - SQL Server 2008 R2 Master Data Services (Master Data Management)

Database Mail Catalog views

Database Mail Catalog views display metadata about the status of e-mail messages processed by Database Mail.

USE MSDB
select * from sysmail_allitems

Contains a row for each attachment submitted to Database Mail.

USE MSDB
select * from sysmail_mailattachments

Contains returned message details for Windows or SQL Server

USE MSDB
select * from sysmail_event_log

Contains a row for each message sent successfully

USE MSDB
select * from sysmail_sentitems

Contains one row for each failed message

USE MSDB
select * from sysmail_faileditems

Contains row for Messages with unsent or retrying status which are still in the mail queue

USE MSDB
select * from sysmail_unsentitems

SQL Server Version Numbering

This defines the product version numbering format for SQL Server. It is MM.nn.bbbb.rr which is defined as:
MM - Major version
nn - Minor version
bbbb - Build number
rr - Build revision number