As we know, SQL Server 2012 (code named “Denali”) was launched this recently. A lot of content is currently available on the Internet that describes the new functionalities and features introduced in SQL Server 2012. However, if you are an ISV and want to certify your database against SQL Server 2012, you need to also know about the deprecated (i.e. removed) features of SQL Server 2012 and their corresponding replacements, if any.

I am therefore presenting a series on the Deprecated Features of SQL Server 2012.

WITH [MEDIA] PASSWORD option for database/log backup & restore

SQL Server 2008 and below allow for configuring a weak password on the entire backup set or media set to prevent unauthorized restores. This password does not protect against overwriting the backup or reading of the backup data.

In order to apply the password, a user would use a syntax similar to the following:

BACKUP DATABASE AdventureWorks2008R2 TO DISK = ‘C:DataAdv2K12.bak’

WITH PASSWORD = ‘Sql@12’

However, starting SQL Server 2012, using this statement will return the following error:

Msg 3032, Level 16, State 2, Line 1
One or more of the options (password) are not supported for this statement. Review the documentation for supported options.

The message clearly states that the option to specify the password for the backup set is no longer supported by SQL Server.

Replacement

No replacement is available from Microsoft against the removal of the feature.

An easy way to identify which feature is deprecated

If you would like to know if your application uses any feature that has been marked as “Deprecated” and if you are as big a fan of the SQL Server Profiler (some shockers on this coming soon) as I am, you can use the “Deprecation” event class when running a trace against your database. Read more about using the “Deprecation” event class here: http://beyondrelational.com/modules/2/blogs/77/posts/11375/sql-server-profiler-part-4-review-t-sql-code-to-identify-objects-no-longer-supported-by-microsoft-de.aspx

Until we meet next time,

Be courteous. Drive responsibly.