I’ve been experiencing an intermittent problem with my automated backup testing routine for some time now. I would occasionally get a failure during a restore, but when I manually ran the exact same restore command, the restore worked without issue. Because my testing routine selects a random sample of backups to test, I was not hitting this error all the time. Being somewhat busy, I put this on my back burner to investigate later.

After the problem happened a couple more times, I realized that the error always seemed to occur with a particular group of databases. The error message in the SQL log said “During upgrade, database raised exception 4147, severity 25, state 1, address 000000000069A96B. Use the exception number to determine the cause.” Googling this exception didn’t return anything useful except for this forum post. At first, I didn’t put two and two together and I didn’t see how this might help me. Then I finally had some inspiration.

Suppose the databases that were failing the restore contained some old style non-ANSI joins, such as *= or =*. These have been deprecated. The message from the forum seemed to imply that restoring a database with these commands manually in SSMS would work, but if you did the same restore in a TRY / CATCH block, it would fail. I was starting to think I had found my problem. When I manually restored the databases, they restored fine. But if my automated routine restored them, which is does inside of a TRY / CATCH block, it fails. I decided to see if I could prove this out. It turns out, I could.

If you want to follow along at home, you’ll need a SQL 2005 server and a SQL 2008 R2 server. This may work on a 2008 server, but I have not tried it. It will probably also work on a SQL 2012 server.

First, on the SQL 2005 machine, run the following to create a test database, two test tables, some fake data, and a stored procedure that uses the old-style ANSI joins. Note that I am explicitly setting the database to SQL 2000 compatibility mode.

/* run this on a SQL 2005 server */

CREATE DATABASE [JoinTest];
GO
USE [master]
GO

EXEC dbo.sp_dbcmptlevel
    @dbname = N'JoinTest'
   ,@new_cmptlevel = 80
GO

USE JoinTest;
CREATE TABLE dbo.Table_1
       (
        Column1 INT NULL
       ,Column2 NVARCHAR(50) NULL
       );
GO

CREATE TABLE dbo.Table_2
       (
        Column1 INT NULL
       ,Column2 NVARCHAR(50) NULL
       );
GO

INSERT  INTO table_1
VALUES  (1, 'Table one-one');
INSERT  INTO table_1
VALUES  (2, 'Table one-two');
INSERT  INTO table_2
VALUES  (1, 'Table two-one');
GO

CREATE PROCEDURE ProcJoinTest
AS
       SELECT   *
       FROM     Table_1 o
               ,table_2 t
       WHERE    o.Column1 *= t.Column1;
GO

If you do not set the compatibility mode to SQL 2000, the CREATE PROCEDURE statement would throw an error: “The query uses non-ANSI outer join operators (“*=” or “=*”). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.”

However, suppose this database has been around a while and has been moved from a SQL 2000 server to SQL 2005 or the server itself was upgraded, so the compatibility mode was at 80 when the procedure was created. After the upgrade, a DBA realized this was an old database and changed the compatibility mode to 2005 (90). Here’s the rub – just changing the compatibility mode will not automatically raise the same error. If that join with the non-ANSI syntax ever gets executed, then the error will be raised, but until then, no warnings are given. So it’s possible you can have this little time bomb sitting in your database for a long time before it blows up with an error.

So, continuing with our experiment, now let’s change the compatibility mode to 90 and make a backup. We’ll drop the database after we are done, as we won’t need it again on this server. Be sure to modify the path to suit your environment.

EXEC dbo.sp_dbcmptlevel
     @dbname = N'JoinTest'
    ,@new_cmptlevel = 90
GO

BACKUP DATABASE [JoinTest]
TO  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Backup\JoinTest.bak';
GO

USE master;
DROP DATABASE JoinTest;
GO

Now copy your backup file over to your SQL 2008 R2 server. We’re going to restore this two ways – 1) using a straight RESTORE statement and 2) using a RESTORE in a TRY / CATCH block.  Again, change the paths to suit your environment.

/* the following command works */
RESTORE DATABASE JoinTest
	FROM DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\JoinTest.bak'
	WITH MOVE 'JoinTest' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\JoinTest.mdf',
	MOVE 'JoinTest_log' TO 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\JoinTest.ldf';
GO

That works. If you look at the output, you can see SQL going through the upgrade steps to convert the database for use on a SQL 2008 R2 system:

Now let’s drop the database and restore it using a TRY / CATCH block:

USE master;
DROP DATABASE JoinTest;
GO

/* the following should fail and hit the catch block */
BEGIN TRY
      RESTORE DATABASE [JoinTest]
		FROM DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\JoinTest.bak'
		WITH MOVE 'JoinTest' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\JoinTest.mdf',
		MOVE 'JoinTest_log' TO 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\JoinTest.ldf';
END TRY

BEGIN CATCH
      SELECT    ERROR_MESSAGE();
END CATCH

USE master;
DROP DATABASE JoinTest;
GO

Look at the output from this command. The upgrade stopped during the conversion from version 628 to 629 and the command aborted.

The only other mention of this behavior I could find on the web was this post, also in a forum. It’s very strange that SQL Server raises an error when doing a restore one way but not another. Hopefully, this post can raise people’s awareness of this little quirk.