Preface:
When you are working with Microsoft SQL Server 2000, you may find it a very cool DBMS as my opinion. But some times, it drives you mad because of some troubles that are hard to find documents or solutions for them. This post reveals one of the most noticeable problem that I have to dealt wit.
Problem:
- How to attach the MDF file on the query analyzer
- How to bring database back on operation when the LDF file is damaged
Solution:
If you need to ignore the current LDF file and if your database is not detached properly and if you do not have the backup, follow the below steps to bring up your database. In the below step-7 is a undocumented DBCC command.
- Create a new database with the same name and same MDF and LDF files
- Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.
- Start SQL Server
- Now your database will be marked suspect
- Update the sysdatabases to update to Emergency mode. This will not use LOG files in start upSp_configure “allow updates”, 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = “BadDbName”
go
Sp_configure “allow updates”, 0
go
Reconfigure with override
GO - Restart sql server. Now the database will be in Emergency mode
- Now execute the undocumented DBCC to create a log fileDBCC REBUILD_LOG(dbname,’c:\dbname.ldf’) — Undocumented step to create a new log file.
(replace the dbname and log file name based on ur requirement)
- Execute sp_resetstatus <dbname>
- Restart SQL server and see the database is online.
Posted by Steve on July 21, 2007 at 8:41 am
Steve
In few years we will see a result
Posted by oldlight on April 8, 2008 at 5:23 pm
The easiest way to solve such the problem is to make sure you always have a full-backup of your database. Otherwise, you’ll have to go through the nightmare of re-take your precious data…
^^
Posted by Prabodh Joshi on December 17, 2008 at 6:40 pm
Dear Oldlight
Thank you for such a great post. You save me lot of time and embarrassment today.
thanks
Posted by oldlight on December 31, 2008 at 12:26 am
You are welcome, Prabodh Joshi
Minh Tran
Posted by Saraboji on February 13, 2009 at 7:09 pm
Mr. Old Night,
You can’t keep every minute and every second full backup. i took the full backup before 1 hour crashing my database. now you tell me what the solution to retrive the one hour data. my database size is 980 GB. Do you know how many hours it will take for full backup.
thanks,
JSaraboji.
Posted by oldlight on February 19, 2009 at 11:56 pm
So you know it, right? Mr. “Sara boy”
)
Posted by CyberTerror on April 26, 2009 at 11:16 pm
Posted by CyberTerror on April 27, 2009 at 7:54 am
Thnk you man
Posted by Mike Gledhill on May 11, 2009 at 7:58 pm
A very interesting article, but I found it’s just as easy to get around the missing-Log-file using this:
In SQL Server Management Studio:
- right-click on Databases and “Attach”
- select the .mdf file for your database.
In the bottom window, it will automatically show details of this selected file, plus the Log file it’s expecting to be associated with it.
- click on the Log file, then click on Remove.
- now click on OK.
SQL Server will happily attach to your database, but create it’s own log file.
Posted by Damerz on June 22, 2009 at 11:46 pm
Oldlight,
EXCELLENT posting, appreciate your sharing of knowledge. I used this procedure successfully today.
Thank you very much.
Posted by GRUMPY OLD DBA on August 6, 2009 at 11:14 am
Sorry, but Caveat emptor
.
In step 5 sysdatabases.status is being updated without having first selected (and keeping) the original sysdatabases.status. A DBA needs to know the original sysdatabases.status, so that database options can be reverted back within a new step (at about 8.5).
Step 7 is a “point of no return”. A REBUILD_LOG also resets the database file’s (the .mdf’s) Log Sequence Number (LSN). Even if you were to subsequently rediscover the old tlog file, it can no longer be “put back” after a REBUILD_LOG. Thus, perhaps a copy of the mdf should first be made, at step 0.
Step 7 fails to mention DBCC REBUILD_Log returns an undocumented prompt to run DBCC CHECKDB. A failure to run CHECKDB at about step 8.25 (whether sp_resetstatus works, or not) is not an “option” IMO. Sure, a DBA can get lucky, in that what the DBA had whacked (out of the tlog via REBUILD_LOG) did not need to be recovered (and thus CHECKDB is error free). But would I gamble my job? I think not
.
When the gamble fails, CHECKDB can report the required repair level is REPAIR_ALLOW_DATA_LOSS. While waiting for CHECKDB REPAIR_ALLOW_DATA_LOSS to whack out no-longer-readable data that may still exist within a “too” old backup (in addition to the “new” transactions which a REBUILD_LOG sent to the big bit bucket in the sky), perhaps that DBA should take the time to polish up their resume …
. To put this another way: I know of more than one DBA who has been fired for following the above. On the other hand, a DBA can follow the above, screw their employer (by allowing data loss), and effectively cover up their tracks (thus retain their job), but the next DBA (seconds to years later) may need to face the music (without knowing why).
If CHECKDB reports REPAIR_ALLOW_DATA_LOSS, I _also_ suggest CHECKCONSTRAINTS be run at about step 8.375. A CHECKCONSTRAINTS can detect orphans caused by REBUILD_LOG if referential integrity is enforced. A CHECKCONSTRAINTS will be unable to detect logical inconstancies caused by REBUILD_LOG when a database design does not enforce referential integrity. A CHECKCONSTRAINTS will not be able to determine whether a CHAR(3) column’s value should be ‘bat’ or ‘cat’.
If CHECKDB reports REPAIR_ALLOW_DATA_LOSS, the data being lost is not necessarily (or is unlikely to be) the “latest data within the now-gone tlog”. Old pages can be split so that new data can be inserted (and this is logged). Thus old pages _can_ be lost. It can take someone who is intimately familiar with not only with schema but also with individual data values to detect this problem (if lucky). Even a cross database select (between a restored old backup and a REBUILD_LOGged database) may _not_ allow the DBA to determine what data was lost: Should that column contain ‘bat’ or should it be ‘cat’?
In retrospect, all it would have taken is any old full backup, plus a complete chain of tlog backups (made since that full backup) to avoid these steps entirely – search SQL Server books Online for “How to restore to the point of failure (Transact-SQL)”, and for further reading search for “point of failure”. Keep in mind the only ‘known good’ backup is one that _has been_ successfully restored. Practice restoring, even if it takes a quick and dirty manual log shipping script (unless one is running 2000’s Enterprise Edition). How far a DBA will go depends upon how much their employer values the cost of insurance