I saw the other thread that is related, but let me give you our circumstances. A drive failed and that drive had the SQL installation and the *.ldf files on it. The MDF files were unscathed.
The drive has been rebuilt and I have reinstalled SQL 2000 SP3A on there (that is what was there before). I have tried attaching the MDF through the GUI and letting it create the log for me. This failed.
I tried sp_attach_single_file_db, but this failed since the db was never dettached.
I tried creating the db again, dettaching it, copying the old (good) MDF file over the newly created one and then attaching (using multiple attach approaches). It knows that the log file and mdf are not matched and won't let me do the attach.
I also found DBCC REBUILD_LOG, but the article in SQL Mag supposes that your instance is still in working order and that there is an entry for the db in sysdatabases. Such is not true in my case.
Can you help? We are trying to get old backup tapes shipped to the site, looking up our Gold Partner contact info, etc., but in the meantime, I am trying to get the data restored even if we lose some transactions.
Thanks for any help!
Here is what worked for me. Thanks for SQL Server Mag for the core pieces of this:
-- LISTING 1: Undocumented DBCC Command REBUILD_LOG
/* Create a new db named the same as the inaccessible one (in this case "Ops") */
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
BEGIN TRAN
UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'Ops'
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
-- 3. Stop SQL Server
-- 4. Replace the newly created Ops.mdf file with the old good one
-- 5. Rename the newly created Ops_log.ldf file
-- 6. Start SQL Server
-- 7. Run the following DBCC command:
DBCC REBUILD_LOG('Ops','E:\Program Files\Microsoft SQL Server\MSSQL\Data\Ops_log.LDF')
-- You should get the message:
-- Warning: The log for database 'Ops' has been rebuilt. Transactional consistency has been lost.
-- DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset,
-- and extra log files may need to be deleted.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-- 8. Run data consistancy check
/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/
DBCC CHECKDB (Ops)
ALTER DATABASE Ops SET MULTI_USER
GO
-- Set database options and recovery model as desired.
GO
-- Make a backup and start to use the DB again
|||Note that in SQL Server 2005, all of this can be accomplished using the
CREATE DATABASE FOR ATTACH_REBUILD_LOG command.
All of the same caveats apply: if you attach a database without a log, you stand a good chance of losing data and/or ending up with a corrupt database. If you've got no choice, you've got no choice. That's what backups are for.
|||dear Mr.BusmasterJones
I know it was a long time since you posted this but can you help me with the same problem but in SQL SERVER 2005
I had a lot of error messages when i TRIED the way you described...
there was an error when I tried the first part and error message was about adding ad hoc is not allowed
then calling the method was rejected too.
Please help me!!!!
I posted this for the same problem http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1976457&SiteID=1
