DB in suspect mode - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

DB in suspect mode

Author

Topic

beza
Starting Member

16 Posts

Posted - 2010-01-26 : 05:11:12

I recently saw that my DB is in suspect mode
I searched for solutions online and I saw scripts turning the DB into Emergency mode and then running dbcc repair
none of the above is working and I am running out of answers
I detached the DB and now I can't attach it back

When I am trying to a restore a backup a took a couple of days ago it doesn't seem to work, I get an exception saying:

The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation'

ANY HELP WOULD BE GREATLY APPRECIATED!!
Thanks!

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 05:28:49

I recommend trying the command using SQL, you are likely to get more informative messages:


RESTORE FILELISTONLY FROM DISK = 'x:\Mypath\MyBackupFilename_Full.BAK'

This should give you the LOGICAL NAMES


USE master	-- (Can't sit in the database whilst its being restored!)
GO

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Restore Full Backup
RESTORE DATABASE MyDatabaseName
	FROM DISK = 'x:\Mypath\MyBackupFilename_Full.BAK'
	WITH
		REPLACE,
		RECOVERY,	-- Use if NO more T/Logs to recover
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'MyLogicalName_Data' TO 'x:\MSSQL\DATA\MyDatabaseName.mdf', 
	MOVE 'MyLogicalName_Log' TO 'x:\MSSQL\DATA\MyDatabaseName.ldf'
GO

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 05:30:20

P.S. You can use a MyDatabaseName that does NOT exist (SQL will create one for you), in which case leave out the ALTER DATABASE to set it to single user as ... it won't exist yet!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-26 : 09:18:00

Too late now, but NEVER detach suspect databases.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 09:31:22

Operating system error 5 is a permission issue. Check that the service account for sql server has os file system priviliges for the file in mind.

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 09:42:52

"Too late now, but NEVER detach suspect databases."

My rule is to never detach a database unless I first take a backup (which then usually makes using the Backup file more useful than Detaching the database at all!)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-26 : 10:03:51

quote:
Originally posted by beza

I recently saw that my DB is in suspect mode
I searched for solutions online and I saw scripts turning the DB into Emergency mode and then running dbcc repair
none of the above is working and I am running out of answers
I detached the DB and now I can't attach it back

*sigh*

Talk about making a bad problem worse.

Emergency mode repair is a LAST RESORT, it is not the first thing to be tried when a DB is suspect.
Never, ever detach a suspect database. It will not reattach.

Do you want help fixing the backup error, or do you want help hacking that suspect DB back in and maybe fixing it? I recommend the backup, unless it's so old there will be unacceptable data loss.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 10:26:54

"Talk about making a bad problem worse."

Although I have sympathy: you have to know that in order to know not to do that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources