How to Rebuild the LDF of a database using MDF file of a database
Some disk bad sectors will corrupt the MDF and LDF files of a database residing on it. Database will function normally even though bad sectors are found in the disk. It will throw some I/O related errors while heavily used in transactions or backing up of database.But when SQL Server gets restarted, this particular database on bad sector disk may or may not come up.
Error Log will show the following error:
Msg 5172, Level 16, State 15,
Line 1
The header for file
'C:\Program Files (x86)\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Log.ldf' is not a valid database
file header. The FILE SIZE property is incorrect.
In the above error, AdventureWorksDW database LDF file is corrupted and database is not coming up.
If we try to attach the database using the MDF and LDF files, it will also fail as shown below
Now the options available is rebuilding the LDF file to bring the database up
or
Creating the New database with the help of backups.
How to rebuild the LDF of database:
Steps:
11.
Go to the path where physical files of the
database resides and move the LDF file from that location to any other secured
location or delete it.
22.
Use the any one of the below scripts:
Script 1:
Create database AdventureWorksDW
ON
(FILENAME = N'C:\Program Files
(x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf')
a.
FOR ATTACH_REBUILD_LOG
Script 2:
sp_attach_single_file_db AdventureWorksDW,'C:\Program
Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf'
Hope the above information helps
Thanks
