Having a problem geting Suspect DB to Emergency...

[omacron]

Hi am trying to change the status of a Suspect DB to Emergency but having an issue. This is MS SQL 2005 Express

When I run this script:

UPDATE master..sysdatabases
        SET status = 32768
        WHERE name = 'CNREMOTE5'

I get the following error:

Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.

Now I thought I could enable this by running this:

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE  sp_configure 'Ad Hoc Distributed Queries', '1'
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

Although that doesn't make a difference.

Basically trying to either run check database commands or export the data into a new database.

Any help

 

[mrdenny]

There are no system tables in SQL 2005.

If there were you would need to enable the "allow updates" option not the "Ad Hoc Distributed Queries" option.

Use the ALTER DATABASE command to enable emergency mode.

ALTER DATABASE CNREMOTE5
SET EMERGENCY;

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[omacron]

thanks that did it

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog