Indefinite hang when restoring SQL 2005 database on a SQL 2008 server in EC2

amazon ec2restoresql-server-2005sql-server-2008

I'm trying to restore a 25 GB database backup taken from a Windows 2003/SQL 2005 machine to a Windows 2008/SQL 2008 machine in the Amazon EC2 cloud, using a .bak file and the SQL Management Studio. SQL Management Studio reports the restore reaches 100% complete, and then just hangs indefinitely (24+ hours) using a lot of CPU, until I restart the SQL Server service. Upon restart, SQL again uses a lot of CPU activity for what seems to be an indefinite amount of time, but the DB never comes online.

Here are some details:
– I have created two EBS volumes, one for DATA and one for LOGS, and I have set the default directories in SQL Server to the \DATA and \LOG directory on these respective volumes. (I wonder if the issue could be related to this, but the DB is too big to restore on the root drive.)
– I have given the SQL Server user group full access to these directories.
– The server can create a new empty test DB in these directories just fine, and can backup and restore the test DB.
– I have tried both restoring of a .bak file and attaching directly to copies of the original .mdf/.ldf files, and the result is the same in both cases.
– Both the .bak restore and the .mdf/.ldf attach occur from/to the EBS volumes.
– I've also tried the above via SQL script, and "WITH RECOVERY", with no difference in the result, just less UI.
– The backup contains two full text indexes.
– I have to use "WITH MOVE" for most of the files in the backup.
– There's nothing wrong with the backup or .mdf/.ldf files, as this works just fine on a Windows 2003/SQL 2005 machine in the Amazon EC2, but not Windows 2008/SQL 2008.
– The DB is NOT marked as "Restoring" in the SQL Management Studio – it is just listed as a normal database, but throws errors when I try to do anything with it (expand the object browser tree, view properties, etc.)

Any ideas?

Best Answer

The database is in the process of being upgraded from SQL 2005 to SQL 2008 when you kill it. Check the ERRORLOG in SQL Server and you should see that the database restore is complete and that the database is being upgraded.

This process is normally very quick, but it can take a while to perform depending on the database, especially if you have a lot of pending transactions in the database which much be rolled forward or backward before the database can be upgraded.