corrupt mdf and ldf file
-
When I had EM open, the SUSDB database had SUSPECT beside it in the EM. In the article it said to detach using the following command. I substituted my db name for pubs obviously.
exec sp_detach_db @dbname = 'SUSDB'
It executed and did not return any errors
Then when I tried the sp_attach_db procedure, that is when I started getting the problems I see now
The command I used there was
exec sp_attach_db 'SUSDB', 'e:\wsus\susdb.mdf', 'e:\wsus\susdb_log.ldf'
I don't understand why it gives me the same error on a different sql server, unless the mdf file is corrupt.
Thanks for all the help from everyone!
-
I have not tried to reset a status for a suspect database before, but there is a stored procedure that will do this (sp_resetstatus). You might want to research this procedure, and then perform a restore of a backup file.
-
I've successfully used this script when our log drive died, taking all the .ldfs with it.
(You might like to try testing this on another machine first.)
--LISTING 1: Undocumented DBCC Command REBUILD_LOGEXEC
use MASTER
GO
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
BEGIN TRAN
UPDATE master..sysdatabases
SET status = status | 32768
WHERE name ='MySuspectDataBaseName'
IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
-- Restart SQL Server at this point.
DBCC REBUILD_LOG('MySuspectDataBaseName','e:\MySuspectDataBaseNamePath\MySuspectDataBaseName_log.ldf')/*Perform physical and logical integrity checks at this point.Bcp data out if your integrity checks demonstrate that problems exist.*/
ALTER DATABASE MySuspectDataBaseName
SET MULTI_USER
GO
-- Set database options and recovery model as desired.
GO
-
The thing about this is that the corrupted database is not on master..sysdatabases anymore, it had been deattached.
Regards
-
I appreciate all the replies, we have now decided to re-install the WSUS server and have it rebuild the database for us. This time it will be added to the backup maintenance plan I have on this server already.
Thanks Again to all!
-
Ive run peagsus script to rebuild a lost log file (drive had corrupt directory).
It worked great.
DB was originally detached. Created a new same name, stop sql, renamed physical files, started SQL. DB was suspect. (no log file). Ran the script, rebuilt log file. and dbcc check db said all ok.
DB looks good.
-
This script with the DBCC REBUILD_LOG is a life saver.
At a client site, some user deleted the ldf file after detaching the database. So I only had the mdf file.
Here is what I done to fix it.
1- Restore latest database backup (it was way too old, but this is another story). Creating the database with same filenames would of probably do the trick also.
2- Shutdown SQL Server 2000 services
3- Rename the database files (mdf & ldf)
4- Put my mdf database file
5- Restarted the SQL Server services which put my db in Suspect mode.
6- Run te magical script (REBUILD_LOG), making sure to modify the parameters before, to reflect my environment.
7- DBCC CHECKDB - everything was fine.
8- Backup the database
9- Database back on track and ready for the users.
** Another thing here, you may have orphane users that you need to fix.
Thanks for everyone. These info were great.
-
fyz (5/26/2008)
Hi,If necessary, I think you can try a utility called Advanced Access Repair to repair your Access MDB file. It works rather well for my corrupt Access MDB files. Hope this can help.
But totally useless for repairing damaged SQL Server databases, since access and sql have very different file structures.
Please stop spamming the forumes with adverts for irrelevent products.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Terry,
Did you tried this:-
1. Create a database with the same name in another directory as the one you're trying to attach.
2. Re-create all filegroups and files as necessary & then Shutdown the server.
3. Swap in the old mdf file and any ndf files.
4. Bring up the server and let the database attempt to be recovered and then go into Suspect.
5. Put the database in single-user mode.
6. Run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
7. Return database to online, multi-user mode.
Manu
-
Hi,
If your database or mdf file is corrupt then you can repair it using stellar phoenix sql recovery software. it can do recovery of tables, views, triggers, and saved procedures.
-
All the answers here still do not address the weird situation, that using sp_attach_single_file_db results in an error on the missing LDF. I have a similar situation, no corrupt database, but a move. Using sp_attach_single_file_db keeps trying to attach the LDF on its old location. Other means of attaching results in the same. No multiple logfiles are involved.
I am curious if something has changed; I'll do some more testing.
Greetz,
Hans Brouwer -
Anyone noticed that this thread is 3 years old?
Hans, can you start a new thread and explain your problem please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply