Attach database from SQL Express 2008 Edition to SQL Server 2008 Enterprise Edition

Question

My friend is stuck on moving a database stored in SQL Express 2008 Edition in a server (called server A) to SQL Server 2008 Enterprise Edition in another one (called server B). There are two SQL 2008 editions installed in the server A.

• SQL Server 2008 Standard
• SQL Express 2008

SharePoint is being run in server A (WFE/Application/Database role) and there are two content databases stored in SQL Express 2008. My friend just got the administration job. He doesn't know exactly what has happened because the ex-staff resigned without any confirmation. At this very moment, he is responsible for moving these content databases from server A to server B (SQL Server 2008 Enterprise is already installed in server B). After doing many times, he still can't attach these content databases to server B. I'm trying to help him by digging into SQL Server. Some errors often occurs:

• Exclusive access could not be obtained because the database is in use.
• The backup set holds a backup of a database other than the existing 'xx' database

Does anyone encounter this case? Any advice is greatly appreciated.

Regards,

T.s

Answer 1

I always like to take the cautious approach to administration.

Before making any changes to the environment, I would suggest your friend take database backups of all relevant databases at source.

Then, rather than using attach/detach. Restore the database to the new instance.

Answer 2

Exclusive access could not be obtained because the database is in use > Try putting your DB in restricted_user mode: alter database_name set restricted_user and then try restoring again.

The backup set holds a backup of a database other than the existing 'xx' database > You may be trying to restore an active DB with another DB. Make sure you want to overwrite the correct DB and then use the replace option in your restore command.

RESTORE DATABASE YOUR_DB
FROM DISK = 'C:\Backups\YOUR_DB.bak'
WITH REPLACE