Restoring a database to a lower edition – SQL Server 2008

>

SQL Server 2008 introduced a few Enterprise Edition-only features which changes the structure of a database when implemented. These features are:

• Partitioning
• Change data capture
• Transparent data encryption
• Data compression

Due to this, whenever you do a database restore or attach on an SQL Server 2008 instance with a lower edition, SQL Server would not allow you.

So what can you do when you have to move a database to a lower edition? Remove the offending features of course, there’s nothing else that you could do. But, how would you know which of the offending enterprise edition-only features are enabled on your database? Use the new dynamic management view sys.dm_db_persisted_sku_features. It’s a simple view which returns the feature you have enabled on your database along with its id. Hence, if the view shows any records, all you got to do now is disable the feature(s) before backing up.

Note: Of course partitioning was present in SQL Server 2005 as well, and even there you had to remove partitioning if you had to restore a database on a lower edition.

>Date of Latest Backup/Restore

>

My colleague who is a developer usually comes up with requests for little bits and pieces of database related information. This time, it was a request for the latest date that a particular database had been restored from a backup. T’was nothing but 10 minutes of BOL and coding, but lot of people out there might find it quite useful on some occasions. So here we go, the first in the series of Not-So-Hip-Snippets-Yet-Useful-At-Times…

A list of database on your instance are stored in the sys.databases table in the master database, while the backup and restore information is stored in the msdb database on several tables:

• For backups: dbo.backupset, dbo.backupmediaset, dbo.backupmediafamily
• For restores: dbo.restorehistory, dbo.restorefile, dbo.restorefilegroup

The database ID is not maintained in the listed tables, hence we would need to join the the sys.databases table with the required tables using the database names. This is the code to get a list of the latest backups performed against your databases:

SELECT
d.[name] AS 'Database Name',
ISNULL(CONVERT(varchar, MAX(b.[backup_finish_date]), 109), 'No backups') AS 'Last Backed up Date/Time',
ISNULL(b.[user_name], 'N/A') AS 'User', ISNULL(b.[type], '-') AS 'Backup Type'
FROM
sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
GROUP BY
d.[name], b.[user_name], b.[type]

While this is the code to get a list of the latest restore operations performed against your databases:

SELECT
d.[name],
ISNULL(CONVERT(varchar, MAX(r.[restore_date]), 109), 'No restores') AS 'Last Restored up Date/Time',
ISNULL(r.[user_name], 'N/A') AS 'User',
ISNULL(r.[restore_type], '-') AS  'Backup Type'
FROM
sys.databases d
LEFT OUTER JOIN msdb.dbo.restorehistory r ON d.[name] = r.[destination_database_name]
GROUP BY
d.[name], r.[user_name], r.[restore_type] 

You could stuff more details into this code, from the tables listed above to suit your requirements.