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.

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.

Haste makes waste – this proverb holds true today also. However, if you think about it hard enough, you may find that haste also teaches us new things, or as it happened recently with me, reminds us of things we already know.

I generally prefer the use of the keyboard to perform most of my tasks because I find that it works much faster for me. Therefore, to execute a query, I use the F5 key instead of pressing the “Execute” button. A couple of days ago, I was working on a query to diagnose a data issue and in my hurry to execute it, I pressed the F5 and the “5” key together (the number 5 is just below the F5 key if you observe).

Obviously, GIGO (“Garbage-In-Garbage-Out”) happened. The query was accidentally modified and this is what was seen:

USE AdventureWorks2008R2
GO
SELECT BusinessEntityID,
       OrganizationLevel,
       5JobTitle              -- Notice the accidental placement of "5" here
FROM HumanResources.Employee
GO

So, What Happened?

What happened here is actually, quite simple. As you can see, SQL Server took the number “5” as the value, and the rest of the string as the column alias. This is because the column alias is a database object name and all object names must follow the rules identified for identifiers.

Database Identifiers

Object identifiers are used to uniquely identify the object. Identifiers comply with the following rules:

1. Identifiers must start with one of the following:
   • A letter as defined by Unicode standard 3.2 (includes a-z, A-Z and letter characters from other languages)
   • The underscore sign (_), at sign (@) or the number sign (#)
2. Subsequent characters can be:
   • Letter as defined by Unicode standard 3.2
   • Decimal numbers from basic Latin or other national scripts
   • The underscore sign (_), at sign (@), the number sign (#) or the dollar sign ($)

3. The identifier must not be a T-SQL reserved keyword

4. Embedded spaces or special characters are not allowed

5. Supplementary characters are not allowed

In our case…

In our case, the column alias was accidentally altered to become “5JobTitle”. Because a column alias is an identifier and cannot start with a number, SQL Server automatically set the value to be “5” and the column name to be “JobTitle”.

Possible solutions

The solutions are many, including:

1. Remove the erroneous number in the column alias
2. Use the AS keyword and define a column alias by using:
   • Double quotes

     USE AdventureWorks2008R2
     GO
     SELECT BusinessEntityID AS "BusinessEntityID",
            OrganizationLevel AS "OrganizationLevel",
            JobTitle AS "JobTitle"
     FROM HumanResources.Employee
     GO

   • Square braces

     USE AdventureWorks2008R2
     GO
     SELECT BusinessEntityID AS [BusinessEntityID],
            OrganizationLevel AS [OrganizationLevel],
            JobTitle AS [JobTitle]
     FROM HumanResources.Employee
     GO

Reference: http://msdn.microsoft.com/en-us/library/ms175874.aspx

So, that was a reminder for me to slow down before I furiously start typing on the keyboard.

Until we meet next time,

Be courteous. Drive responsibly.

Haste makes waste – this proverb holds true today also. However, if you think about it hard enough, you may find that haste also teaches us new things, or as it happened recently with me, reminds us of things we already know.

I generally prefer the use of the keyboard to perform most of my tasks because I find that it works much faster for me. Therefore, to execute a query, I use the F5 key instead of pressing the “Execute” button. A couple of days ago, I was working on a query to diagnose a data issue and in my hurry to execute it, I pressed the F5 and the “5” key together (the number 5 is just below the F5 key if you observe).

Obviously, GIGO (“Garbage-In-Garbage-Out”) happened. The query was accidentally modified and this is what was seen:

~~~USE AdventureWorks2008R2
GO
SELECT BusinessEntityID,
OrganizationLevel,
5JobTitle — Notice the accidental placement of "5" here
FROM HumanResources.Employee
GO~~~

So, What Happened?

What happened here is actually, quite simple. As you can see, SQL Server took the number “5” as the value, and the rest of the string as the column alias. This is because the column alias is a database object name and all object names must follow the rules identified for identifiers.

Database Identifiers

Object identifiers are used to uniquely identify the object. Identifiers comply with the following rules:

1. Identifiers must start with one of the following:
   • A letter as defined by Unicode standard 3.2 (includes a-z, A-Z and letter characters from other languages)
   • The underscore sign (_), at sign (@) or the number sign (#)
2. Subsequent characters can be:
   • Letter as defined by Unicode standard 3.2
   • Decimal numbers from basic Latin or other national scripts
   • The underscore sign (_), at sign (@), the number sign (#) or the dollar sign ($)

3. The identifier must not be a T-SQL reserved keyword

4. Embedded spaces or special characters are not allowed

5. Supplementary characters are not allowed

In our case…

In our case, the column alias was accidentally altered to become “5JobTitle”. Because a column alias is an identifier and cannot start with a number, SQL Server automatically set the value to be “5” and the column name to be “JobTitle”.

Possible solutions

The solutions are many, including:

1. Remove the erroneous number in the column alias
2. Use the AS keyword and define a column alias by using:
   • Double quotes
     ~~~USE AdventureWorks2008R2
     GO
     SELECT BusinessEntityID AS "BusinessEntityID",
     OrganizationLevel AS "OrganizationLevel",
     JobTitle AS "JobTitle"
     FROM HumanResources.Employee
     GO~~~
   • Square braces
     ~~~USE AdventureWorks2008R2
     GO
     SELECT BusinessEntityID AS [BusinessEntityID],
     OrganizationLevel AS [OrganizationLevel],
     JobTitle AS [JobTitle]
     FROM HumanResources.Employee
     GO~~~

Reference: http://msdn.microsoft.com/en-us/library/ms175874.aspx

So, that was a reminder for me to slow down before I furiously start typing on the keyboard.

Until we meet next time,

Be courteous. Drive responsibly.

The graphical utilities and tools within SSMS are a boon for all the application developers and "accidental" DBAs. Often developers need to perform basic DBA activities like a database backup or a restore on local copies of their database. Similarly, the accidental DBAs are acting as a substitute for a DBA who is on leave or unavailable and would like to be as safe as possible. Instead of writing T-SQL code, it is much easier, faster and reliable for them to use the SSMS.

The problem

These usage patterns often expose interesting issues with the design of a product. In June 2011, I had written about an issue that some of the developers in our team were facing – SSMS – Database Restore – Physical File Names should default to Logical File Names – MS Connect Case #668566. For those who work with database backup/restore operations frequently, it is common place to know that:

1. If the paths where the source database files existed are available on the destination file system, the SSMS would set these paths and file names by default
2. If these paths are unavailable, then the default paths are used
3. If the default paths are used, then the file names default to the database name

The problem with this approach is that all data files – primary and secondary – receive the same file name and are configured, by default, to be restored on the same path. The restore would fail if one accepts the defaults set by the Restore wizard.

In relation to this issue, I had logged a Microsoft Connect bug report#668566, which has been fixed in SQL Server 2012 (“Denali”). Today, I will attempt to quickly walk you through the changes made to the restore database UI.

Database Restore Wizard Changes in SQL 2012

Let’s assume that I have a test database, NAVTestDB, on one of my servers at the location C:TestDB with file names – NAVTestDB1.mdf, NAVTestDB2.mdf and NAVTestDB.ldf for the primary and secondary data files and log file respectively.

USE NAVTestDB
GO
SELECT * FROM sys.database_files sdbf ORDER BY sdbf.type asc

Now, let’s take a backup of this database, and move to a different instance of SQL Server 2012 hosted on a machine where the path: C:TestDB is not available. Once the backup file has been moved to the new/destination server, let’s attempt to restore this database using the restore database wizard.

The restore database wizard now has a status bar on top and a more logical interface: 1. User would first select the source to be used for the operation 2. Then supply the destination database 3. Finally select the backup sets to restore

The backup set selection dialog has changed! We now have a familiar dual-pane interface.

The system attempts to read the following from the backup file: 1. Header information 2. Label information 3. Filelist information The user is now aware that an internal operation is going on by the availability of a progress bar within the status bar on top.

Once the backup set information is retrieved, the user can attempt to verify the backup set by clicking on the “Verify Backup Media” button. Confirmation about the verification is available on the status bar. You can read about RESTORE VERIFYONLY from my post here.

FILES Tab: Notice that the user can now choose to relocate the files by checking the “Relocate all files to folder” checkbox and choosing the required path. Also, note that the wizard now shows us the original path & file name and the proposed path & file names. NOTE: Observe that we now have unique file names preventing the original issue that we started off with.

OPTIONS Tab: Moving to the Options tab, we can see that the wizard is more powerful than ever before. The user now has the ability to choose: 1. Recovery state (WITH RECOVERY/NORECOVERY/STANDBY) 2. Specify a standby file 3. Perform a tail log backup before attempting a restore Associated quick help is available in the label boxes below.

Clicking on “OK” begins the restore, however, the user now has the chance to stop the restore in progress.

Conclusion

SQL Server 2012 (“Denali”) comes with a lot of new features and enhancements targeted towards making the lives of developers and administrators more simpler and easier. I hope that you, my respected readers will be able to make maximum use of these new features.

Until we meet next time,

Be courteous. Drive responsibly.