SQL SERVER 2000: Recovering Database from *.mdf file

Below is another solution how to recover a Database by using MDF file.

==================================================


/*
1)------ Very Important Step ------ create a new database of the same name and log file and location as the old database and log file
get rid or rename the old database.

2)----- Very Important Step ------ create a new database of the right size and shape with correct log and data file locations

3) stop sql server in service manager

4) rename the new databases.mdf or delete it if you don't have enough space - do not touch the .ldf

5) move back in the old database .mdf file or rename it back again

6) restart sql server

7) it should come up suspect

8) After this run the below query in the master DB

-- Note -- I have used 'ABCDEF' as an example in the below query, replace it with the DB which you want to recover.
*/


EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE filename = 'E:\Data\ABCDEF_Data.MDF'

IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- Restart SQL Server at this point.

DBCC REBUILD_LOG('ABCDEF','E:\Data\ABCDEF_Log.ldf')


/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/

ALTER DATABASE ABCDEF SET MULTI_USER
GO


GO

==================================================

here 'E:\' is my drive where the data is stored

SQL SERVER 2000 - Database recovery from an *.MDF file

This is another problem which had bothered me alot. Database log file went corrupt, even though I had a backup of the databases (IMP: always keep a backup), the problem was still in my mind looking for a solution for this. With the help of other fellow engineers in the forums for which I had surfed, I found the way to create the *.ldf file using the *.mdf file. Below I have described how to go about it.

================================================
/*
1) ------ Very Important Step ------ create a new database of the right size and shape with same name of log and data and the file locations, of the corrupted DB.

2) stop sql server in service manager

3) rename the new database.mdf (or delete it if you don't have enough space) -- do not touch the .ldf

4) move back in the old database .mdf file or rename it back again

5) restart sql server

6) it should come up suspect

7) After this run the below query in the master DB

-- Note -- I have used 'ABCDEF' as an example in the below query, replace it with the DB which you want to recover.
*/


EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE filename = 'E:\Data\ABCDEF_Data.mdf' ------- Changes to be done

IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- Restart SQL Server at this point.

DBCC REBUILD_LOG('ABCDEF','E:\Data\ABCDEF_Log.ldf') ------- Changes to be done


/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/

ALTER DATABASE ABCDEF SET MULTI_USER ------- Changes to be done
GO


GO
=================================================

well thats it your have your old database back online.

Here (E:\) is my drive in which the data is stored.

Will be posting more errors and solutions

hi, below is only one of the problems i have faced....there were lot of problems and issues i had faced and will be posting more of the issues and solutions. anyone can comment so that i can post your requests and solutions.

Cannot Install Windows on SATA HDD - Disk not found.

This was one of the problem i had faced while installing WindowsXP on my bro's machine. He had bought a 400gb WDSATA Disk. His pc config was AMD ATHLON XP, with 1GB Ram and VIA chipset....for a solution i tried everything from changing the SATA cables to power supply.Finally I explored and found that there is a Raid Controller FDD, that needs to be installed. During installation of windows it will say 'Press F6 to install additional devices such raid' ...etc..
Press F6, then on the Next screen install the FD into the floppy Disk drive and press S, to specify the addtional device and then continue with the installation. Your SATA HDD will be detected.


i know many of you might know the solution, but still i have posted it, since there are guys out there looking for a solution.