SQL Server World

Today one of our application team requested to build new sql server 2008 R2. I asked them about the edition. They said only requirement is it should be able to handle 32 GB of RAM, other than that we are not using any high availability features. I installed sql 2008 R2 Standard. It supports up to 64 GB of memory. We moved the 6.5 TB database from the older machine having SQL2008 Developer Edition to new machine. This is how it was done. We first detached the existing database, shutdown sql server and moved the powervault containing data and log files to the new server. We brought the new server online. Sql server service was set to automatic start on the new server. Now we can see the database but cannot see it properties, cannot drop it or detach it either. Error said “The database is suspect. In SQL Server 2005 and later versions, a suspect database cannot be detached; before you can detach it, you must put it into emergency mode.”  I checked the state of database. “Select state_desc from sys.databases where name = ‘mydatabase’ “. It showed “Recovery Pending”. Another error said something like this “This database uses compression and this version of sql server does not support compression. Need to Upgrade”. We got the point. We tried to put the database in emergency mode  “Alter database mydatabase set emergency” but it complained “ it has to be in single user mode”.

So we put it in single user mode. “Alter database mydatabase set single_user”. Then we put it in emergency mode. Again I checked the state of database. “Select state_desc from sys.databases where name = ‘mydatabase’. It showed “Online”. Now I tried to detach the database and it complained “database is in single user mode, cannot detach”. Ok, we put it in multi_user mode and then detached successfully using GUI.

After this I upgraded using the maintenance option of setup GUI and upgraded it to Enterprise Edition. Then I attached the database using the GUI and 6.5 TB database came on line in less than 15 seconds. Altogether it took me 20 minutes. I like to give credit to Paul Randal’s (@PaulRandal) article http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx whithout which it would have taken us much longer.