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.
| Author |
Topic |
|
travisl
Starting Member
12 Posts |
 Posted - 2004-12-28 : 22:56:08
|
| Hello, I'm working on a situation where we need to recover from a failed system. The database was simple, in that it had one MDF and one LDF, each on it's own disk.The disk with the LDF failed, and had to be replaced.The database comes up as "suspect" because the LDF no longer exists.I have found a fix "sort of" but I'm hoping there is a better way. Here is what I've been able to do so far:1) I do a backup of "the tail of the log." I know, the log doesn't exist, and it fails, but something magic happens. This procedure doesn't work without this step.2) I detach the database.3) I reattach the database. A new LDF is created for me.4) The database seems to be fine, including updates from before the crash.The problem is, this method of auto-generating the LDF file causes it to be built in the same location as the MDF, not in it's preferred location on the separate disk...Also, I'm sure there are other problems I'm unaware of?Can someone offer me a better solution? Is RAID really the only way to protect yourself from disk failure on the LOG disk?Thanks,TravisTravis |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-12-28 : 23:34:11
|
| Travis,A few things to clear up first:1) Nothing magic happened. You lost any transactions that were "in progress" and hadn't actually committed. In addition, you lost the history the log would hold for you next transaction log backup.2) The new database is created in the default location you have listed in the Properties on Enterprise Manager. You can also find it by running the same procedures EM runs when it looks up the information:xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog'or change it the same way:xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData',REG_SZ,N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\'In addition, after the creation of the database, you can move the files by following this article:http://support.microsoft.com/kb/224071/EN-US/MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
travisl
Starting Member
12 Posts |
Posted - 2004-12-29 : 05:29:56
|
| Derrick, thanks for the quick help!Would you say that this is the standard means of recovery from a failed LOG disk, if no mirroring was in place?TravisTravis |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-29 : 06:10:31
|
| If you lose a log file it's a good thing to try.If it doesn't work thenhttp://www.mindsdoor.net/SQLAdmin/RecoverCorruptDatabase.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
possible to recover from failed LOG disk?