2

I have SQL Server 2000 (aka version 8.0) that failed to recover a database after a power failure. The only backup I found are the current mdf file and an ldf file that is two years old.

Can I restore the database using the current mdf file and an old ldf file?

2
  • "The only backup I found are the current mdf" - meaning the one live one that failed after the power failure?
    – J.D.
    Oct 27, 2022 at 3:38
  • Yes. The last backup today. There is a mdf from yesterday and another mdf today. The system failed yesterday after office hours (we are a small school)
    – Si Vic Ako
    Oct 27, 2022 at 3:51

1 Answer 1

5

Ignore the two-year old LDF file, it isn't relevant at this point.

Your only option is to re-attach the MDF, without the LDF, and get SQL Server to rebuild it.

I advise you to make a copy of your MDF at this point, in case anything else goes wrong.

CREATE DATABASE YourNewDb
ON (FILENAME = 'YourMDFHere') 
FOR ATTACH_REBUILD_LOG;

If the database was not checkpointed and shutdown cleanly then this may fail. In which case you can use the undocumented FOR ATTACH_FORCE_REBUILD_LOG. You will lose any in-flight transactions, including already-committed transactions that have not yet been checkpointed.

The state of the database may be inconsistent if there were any transactions not checkpointed. For example, foreign keys may be out of whack, and other internal system tables maybe incorrect.

DBCC CHECKDB ('YourNewDb')
  WITH EXTENDED_LOGICAL_CHECKS, TABLOCK, NO_INFOMSGS;

Another option to rebuild, without using any undocumented features is to create a new database and copy the files in. Begin by creating a new database not using the original files.

CREATE DATABASE YourNewDb
ON PRIMARY (
    NAME = N'LogicalMDFName', 
    FILENAME = N'NewMDFFileHere'
) 
LOG ON (
    NAME = N'LogicalLDFName',
    FILENAME = N'NewLDFFileHere'
);

ALTER DATABASE YourNewDb SET OFFLINE WITH ROLLBACK IMMEDIATE;

Copy the old MDF file into the new location. Then do the following:

ALTER DATABASE YourNewDb SET EMERGENCY;
ALTER DATABASE YourNewDb SET SINGLE_USER;
DBCC CHECKDB ('YourNewDb', REPAIR_ALLOW_DATA_LOSS)
  WITH EXTENDED_LOGICAL_CHECKS, TABLOCK, NO_INFOMSGS;
ALTER DATABASE YourNewDb SET MULTI_USER;

You may also have logical inconsistencies in your data which CHECKDB cannot verify. For example, you may have a history log table showing an action by the user, but the action is not present in its own table, or you may have later but not earlier rows. You will need to verify this yourself.


Final notes:

  • Always, always, always, always, always, always take backups and test them.
  • MDF and LDF files are not backups. SQL Server has very good built-in backup systems, which allow you to take full, diff and log backups, and make point-in-time restores.
  • SQL Server 2000 was end-of-life'd almost 10 years ago. It is high time you upgraded, as it is now missing many security and performance updates, and missing many new features.
3
  • I used your script as follows: CREATE DATABASE MyDatabaseName ON (FILENAME = 'MyDatabaseName.MDF'') FOR ATTACH_REBUILD_LOG; It came out with this error message: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'ATTACH_REBUILD_LOG'.
    – Si Vic Ako
    Nov 3, 2022 at 3:10
  • You have two apostrophes '' after the filename, instead of one. Nov 3, 2022 at 13:02
  • Thanks, Charlieface! the data was successfulyy restored. Thanks for the advise, too.
    – Si Vic Ako
    Nov 11, 2022 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.