December 26, 2002 at 1:13 pm
We get an error when we try to reattach a certain database with its log. So we attempt to attach single file, without log, and it tries to create a log, and errors saying that file doesnt exist. Well, it doesnt yet. So my thinking is this... is there a way to creata a blank log file to be used with this attach? so that the mdf can still be attached? We've attempted in the past to copy other ldf's and simply rename them, in a vain attempt at messing with sql servers mind, but it don't work.
Any ideas? We know that this database is suspect. It was detached and copied after it was suspect. But how do we restore it so we can see what happened inside? This is for learning purpose only, original database was restored from a backup and they are now running, we just wanted to play with this suspect one to learn.
thanks
December 27, 2002 at 9:49 am
How big is this? Is it sensitive data? could you send it? (Pls reply before sending).
I'm surprised you can't attach with no log file, usually works, though suspect status may prevent this.
Is this the same server or a different one? Have you tried a different one?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 27, 2002 at 12:24 pm
You can try DBCC Rebuild_log, one of the many undocumented ones. Have it on my list to experiment with, have not done so yet.
Andy
December 30, 2002 at 10:00 am
The database is not attached, so we cannot try dbcc rebuild_log. But we were finally able to reattach it by creating a new blank db, stopping the server, then renaming our goofed up mdf to the new db name. Restart the server, and then play with stuff like status, etc. until it was up again.
December 30, 2002 at 10:01 am
Wow, not a lot of fun, but a nice creative solution.
Thanks for the update and if you don't mind, can you post exactly what you had to run to clean up the status?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 30, 2002 at 11:04 am
I'll have to look for where I saw the rebuild log originally, but I think you do have to do the step of creating a db, stop/start service, other voodoo.
Andy
December 30, 2002 at 1:48 pm
A coworker of mine found the solution authored by Kevin Sun (kv_sun@microsoft.com). He did a google search and found it.
-------------
Restore data when ldf is corrupt and sp_attach_single_file_db won’t work.
Did you delete the log file after the space of disk is full? If the data in .mdf is corrupted, it is difficult to restore all of the data, if it is only related to losing of .ldf file, we can try to the following steps:
=================
--create a new db and stop SQL server.
--rename its .mdf file (test.mdf), then rename the suspect mdf to original name of .mdf file of the new db,
--restart SQL server, the new db will be suspected status (since the .mdf file is not consistence to .ldf file, if recovery mode is full)
USE master
GO
sp_configure 'allow_updates',1
reconfigure with override
GO
-- change the status of the db to emergence status:
SELECT * FROM sysdatabases WHERE name ='mysuspectDB'
BEGIN TRAN
UPDATE sysdatabases SET status = 32768 WHERE name ='mysuspectDB'
COMMIT TRAN
--rebuild a new log using the following command
-- you will want to delete the old .ldf before running this line
DBCC REBUILD_LOG ('mysuspectDB','f:\temp\mysuspectDB_LOG.ldf')
--checkdb to make sure there is no consistence problem to the db
DBCC CHECKDB ('mysuspectDB')
--change the status of the db to normal
UPDATE sysdatabases SET status=0 WHERE name ='mysuspectDB'
SELECT * FROM sysdatabases WHERE name ='mysuspectDB'
USE master
GO
sp_configure 'allow_updates',0
reconfigure with override
GO
--restore the .log file with recovery parameter
RESTORE DATABASE mysuspectDB WITH recovery
---then refresh or restart the SQL server, the db should be available for now.
=====================
If the suggestion above do not work, you may have to try to retrieve the
data from the "corrupted" db using BCP under emergency status.
This is similar to the above – but the status is changed to 32768 instead of 0. This allows reading the data – but no updates are allowed. You can either select from the tables individually or through a DTS transfer. If you are restoring for a database where the name is important, remember to attach the MDF using a database name that is a temporary name because you will want to use the real name for the working database. This may require renaming the disk file as well.
December 30, 2002 at 6:11 pm
Cool, thanks for finding it! Gotta find a way to avoid the stop/start service thing though.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply