SSMS Fails to restore with SQL 2000 instances and SSMS Versioning Explained….
I recently have been on vacation. When I returned I noticed an interesting issue that deserves some details and explanation in case you encounter the problem. As part of this explanation, I’ll give you some information about how the “version” of SQL Server Management Studio is determined.
A problem was recently discovered when using SQL Server Management Studio (SSMS) for SQL Server 2005 or 2008 to restore a SQL Server 2000 database backup. This problem only occurs if you use SSMS to restore the SQL Server 2000 backup on a SQL Server 2000 Server. In other words, you use SSMS Object Explorer to connect to a SQL Server 2000 instance and use the SSMS dialog boxes to restore the SQL 2000 backup on the SQL Server 2000 instance. Once I describe the cause of the problem, you will understand why it is narrowed to only this situation. The probably also only occurs with a specific sequence of choices in SSMS and only if you have applied one of a few specific cumulative updates. I’ll explain the versioning later in this post.
For the official KB article that describes this problem, see https://support.microsoft.com/default.aspx?scid=kb;EN-US;972687
The Problem Experience
Let’s say as a DBA you want to use SSMS for SQL Server 2008 to manage a SQL Server 2000 instance. Certainly reasonable as you may have a legacy application that only is supported on SQL Server 2000. So you register the SQL Server 2000 instance on SSMS on your computer. You discover a problem with one of the databases and need to restore a backup of a database on top of an existing database in the Database tree (this is important because this is the only place you encounter the bug). Look at this screenshot:
On my SQL 2000 instance I have a database called pubs. Let’s say I want to RESTORE on top of the existing pubs database from a backup. To do this with SSMS, I right-click pubs and select Tasks and Restore like this:
If I happen to be running on a version of SSMS (I’ll get to how you can figure this out) that has this problem, I’ll get a dialog box that looks like this:
What do I do Now?
If you encounter this problem, you have two choices: 1) Avoid it 2) Get a Hotfix:
- Avoid It
Two ways to avoid this:
1) Just use T-SQL RESTORE from a Query Window or with SQLCMD. Not a great option if you want to use the GUI but a quick temporary measure to get your database restored.
2) Restore using the “top-level” Databases node in Object Explorer like this:
Right-click the Databases node and select “Restore Database”
In the next screen, be sure to pick From Device in the Source for Restore section.
- Get a Hotfix for the Problem
I wish this set of steps was simple but unfortunately it requires some work on your part. If you look at the KB article listed at the top of this post, it shows what version of SSMS is affected by this problem:
SQL Server 2005 Service Pack 2 (SP2) Management Studio, version 9.00.3325 to 9.00.3329
Note For example, you install SQL Server 2005 SP2 Cumulative Update 13, and the version of Management Studio is 9.00.3325.SQL Server 2005 Service Pack 3 (SP3) Management Studio, version 9.00.4220 to 9.00.4228
Note For example, you install SQL Server 2005 SP3 Cumulative Update 3, and the version of Management Studio is 9.00.4220.The release version of SQL Server 2008 Management Studio, version 10.00.1806 to 10.00.1813
Note For example, you install SQL Server 2008 Cumulative Update 5, and the version of Management Studio is 10.00.1806.SQL Server 2008 Service Pack 1 (SP1) Management Studio, version 10.00.2714 to 10.00.2725
Note For example, you install SQL Server 2008 SP1 Cumulative Update 2, and the version of Management Studio is 10.00.2714Here is the problem with this table. What is the version of SSMS installed on your machine? This is not an easy thing to figure out as it turns out (and we need to make this better). I have SQL Server 2008 Cumulative Update 3 for SP1 (10.0.2723 according to @@version) installed, but if you select Help..About on SSMS you get something like this:
For SSMS it says 10.0.2531, which is SP1 for SQL Server 2008. But remember I said that my @@version shows 10.0.2723 which is CU3 for SP1. So what gives?
Well technically the version in the Help..About is correct. This is reporting the version of the assembly AppIDPackage.dll which ships with SSMS.EXE. This can be found in the same location as SSMS.EXE which is typically C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE
NOTE: Since SSMS.EXE is a 32bit managed application as well as its dependent assemblies, on a 64bit box this will be located at c:\program files (x86)\… Another note here is that SSMS is called SQLWB.EXE for SQL Server 2005.
You may wonder how I found this. I used Task Manager for SSMS.EXE and right-clicked to select Open File Location), This Managed Application depends on other assemblies that are shipped as modules. Some of these are in this same directory such as AppIDPackage.dll. Others can be found at C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies or C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies. If you look further in the KB article (we updated it recently) it mentions that the problem and fix for this issue is actually in an assembly called Microsoft.SqlServer.SqlEnum.dll. This is because this is the assembly that needed to be changed to make the fix.
So if you need to see what version you have installed of this assembly that could affect this problem, you need to look at the properties of this Assembly, which on the machine I’ve installed CU3 for SQL2008 SP1 is 10.0.2723.
So if you go back to the list above, you can see that my machine is subject to this problem because my version of this assembly is 10.0.2723 which matches to:
SQL Server 2008 Service Pack 1 (SP1) Management Studio, version 10.00.2714 to 10.00.2725
So now I know I need to apply a fix, where do I get it? Well, as you see multiple version of this assembly have been affected by this problem across both SQL Server 2005 and 2008. We can’t just ship one version of the fix to help everyone. We have the FIX article setup so all the appropriate fix choices are available when you select
at the top of the article. The problem is you must figure out which one to apply for the version of your assembly. Another problem is that at the time of this blog post (August 20, 2009), we don’t have cumulative updates to fix this problem for SQL Server 2008. We only have “On-Demand” fixes which are typically only released based on customer demand. This is because when we found this problem we were the process of working on new cumulative updates for RTM and SP1, but they are not ready yet. When these cumulative updates are available, the KB article and list of fixes to be applied will be updated.
This is why the table of what fixes to apply in the KB article looks something like this:
SQL Server Release Cumulative Update Resolution SQL Server 2005 SP2 9.00.3325 to 9.00.3329 Apply SQL Server 2005 SP2 Cumulative update package 15. SQL Server 2005 SP3 9.00.4220 to 9.00.4228 Apply SQL Server 2005 SP3 Cumulative update package 5. The release version of SQL Server 2008 10.00.1806 to 10.00.1813 Apply the individual hotfix for the release version of SQL Server 2008. To obtain this hotfix, refer to the "Hotfix information" section in this article SQL Server 2008 SP1 10.00.2714 to 10.00.2725 Apply the individual hotfix for the SQL Server 2008 Service Pack 1. To obtain this hotfix, refer to the "Hotfix information" section in this article. A few things to note here:
- A version range is listed for each of these because the problem exists in multiple versions of a given service pack. For example, for SQL Server 2005 SP2, the problem exists in CU13, CU14, and any other possible on-demand build shipped in between these or before CU15. But 9.00.3229 is the end of the range because CU15 for SQL 2005 SP2 is 9.00.3330.
- In the Resolution section note that a cumulative update is not listed for SQL Server 2008. This is due what I had explained above. We don’t have a CU available yet for SQL Server 2008 RTM or SP1 that addresses this. But we do have an “on-demand” fix available should you need it.
- All the current fixes are available when you click on “View and request hotfix downloads” at the top of KB article https://support.microsoft.com/default.aspx?scid=kb;EN-US;972687
I’m using SSMS to manage a SQL Server 2000 instance but I haven’t hit this problem?
This can only be from one of the following reasons (assuming you are truly restoring a database from SSMS for a SQL 2000 instance):
1) You haven’t tried to restore a database using the specific steps from SSMS as listed in the post.
2) You have a version of Microsoft.SqlServer.SqlEnum.dll that does not fall into the range as listed in the KB article. For example you are using SQL Server 2005 SP3 but have not applied Cumulative Update 3 or 4.
3) You have already applied a cumulative update or on-demand fix that resolves the problem. Check the version of Microsoft.SqlServer.SqlEnum.dll. It probably is outside of the range listed in this blog and article.
Why did this Happen?
We had a customer report a bug with SSMS when restoring a backup as documented in this KB article: https://support.microsoft.com/kb/965217/. The problem here was a query we were generating for msdb backup metadata if a database had been forked. So we put this fix in place and delivered it in CU13 for SQL Server 2005 SP2. But we also ported this change to go into future updates for SQL Server 2005 SP3, SQL Server 2008 RTM, and SQL Server 2008 SP1. So anyone applying updates from this point forward for these versions would pick up this fix. Everything seems fine. Makes sense.
One problem. The fix we made involved a change to the queries to account for recovery forks which included references to columns in msdb.backupset which only exist in SQL Server 2005 or later and use of operators like TOP(1) which only exist in SQL Server 2005 or greater. So… if SSMS tries to send these queries to a SQL Server 2000 instance, it will fail with a syntax error. (If you look at the dialog box error you can see Msg 170 syntax error is what the engine returns).
Conclusion
We definitely would have like to avoid a problem like this from occurring and feedback into quality assurance for this has been done. The one feedback I’ll spend more time on is how to show version information better for SSMS in the future. That certainly caused some confusion when we first released the KB article and has probably caused some confusion in other situations.
Bob Ward
Microsoft