Troubleshooting: Cannot open database “AED XXX”…

Question: Failed to login, message: "Cannot open database AED_...."

Version: 1.8 / 1.9 / 2.0

Answer:

This message is prompted for a few possible reasons, two of the most likely reasons are:

The database has not been attached to SQL Server, OR more accurately, has been detached from SQL Server.)
The database attached to SQL Sever has been marked as 'Suspect' by SQL Server.

Note: To determine if a database status is marked as Suspect, take a look at the name of database at Microsoft SQL Server Management Studio Express...(also refer to Unable to attach suspected database)

The following steps are recommended to repair a Suspect database (that are recoverable).

Go to Microsoft SQL Server Management Studio Express (Go to Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express, click on Connect …. )
Click on New Query, key in the following queries, then Execute.

? EXEC sp_resetstatus ‘AED_ABC’ -- this query is to reset the 'Suspect' status, or turn off the Suspect flag on a database.
? ALTER DATABASE AED_ABC SET EMERGENCY -- this query is to allow SQL administrator to take control of the database for trouble-shooting purpose.
? ALTER DATABASE AED_ABC SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- this query will set database to allow only one user at a time access to the database.

3. Clear the contents of Query box, and key in the following query, follow by Execute:

? DBCC CHECKDB (‘databasename’, REPAIR_ALLOW_DATA_LOSS) -- this query is used to check the errors and fix/repair them.

Note: there are 2 more modes of repair that can be used in certain occasions that involved only minor errors or and re-indexing: (1) DBCC CHECKDB (‘databasename’, REPAIR_FAST) -- used to perform minor, non time-consuming repair actions such as repairing extra keys in non-clustered indexes. These repairs can be done quickly and without risk of data loss. (2) DBCC CHECKDB (‘databasename’, REPAIR_REBUILD) -- to perform all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.

? Query completed with errors... scroll downwards to read through the message.... you may see some lines saying errors were found and fixed/repaired... ? Click on Execute again to ran the query (you may need to do this for several times) until the message showing: Query executed successfully.

4. Clear the contents of Query box, and key in the following query, follow by Execute:

• ALTER DATABASE AED_ABC SET MULTI_USER -- this query will set the database back to multi-user mode to allow connection of all users that have appropriate permission.

5. Now, you should be able to login the account book.

By:Peter@2013; P170801