Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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.

share|improve this answer
    
Ok, I can buy that there is an upgrade going on. But... the log file is not huge (200 MB), the original DB was not particularly active at the time of the backup (if at all), and more than a day of upgrade time on a reasonably powered server seems very excessive. - How long should I expect the upgrade to take? - What are the factors that affect upgrade time? - Most importantly, I can't allow a day or more of down time when I attempt this in my production environment - what strategy will reduce the time needed for upgrade? –  Erin Loy Jan 19 '10 at 21:24
    
Like I said, it normally is pretty quick. Is there anything logged to the ERRORLOG to indicate that the upgrade is taking place? How about if you run the restore from T/SQL directly? At what point does it stall? Try running the restore with the stats=1 flag and it'll output the status as each percent of the restore is completed. Also make sure that there is no blocking going on when you run the restore. There isn't anything which can reduce the time to restore. –  mrdenny Jan 21 '10 at 9:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.