I am working on a client who running Microsoft SQL Server 2008 R2 Express that implented in Retail stores.

The database architecture setup was very unique due to the nature of business.

Each retail store has itown SQL Server 2008 R2 Express that connected to POS machine.

However, the database recovery model was setup to be SIMPLE.  (It’s not a best practice) in case a disaster.

In addition, the log file was named with extension MDF instead of LDF.

One day, the database was corrupted and down for 4 days.  The company DBAs was unable to recover the database.

I was assigned to recovery those SQL database.

Here are my steps to recover the corrupted database.

—- Step Create the exact database name

—- Step 1 Create the exact database name

CREATE DATABASE AxRetailPOS ON  PRIMARY

( NAME = N’AxRetailPOS’,

    FILENAME = N’D:\SQL_DATA\DATA\AxRetailPOS.mdf’,

    SIZE= 8MB, MAXSIZE = UNLIMITED, FILEGROWTH = 16MB)

LOG ON

( NAME = N’AxRetailPOS_log.mdf’,

 FILENAME = N’E:\SQL_LOG\AxRetailPOS_log.mdf’,

SIZE = 8MB, MAXSIZE = UNLIMITED, FILEGROWTH = 16MB)

GO

—- Step 2

Stop the SQL Server Services

Move the original MDL file from older server (or location) to new server (or location).

By replacing just create MDF file.  Then, delete the LDF file of the new server just created.

— Step 3

Started the SQL Server services.

— Step 4

Make sure system table of master database allow to update the value

USE MASTER

GO

 exec SP_CONFIGURE ‘allow updates’, 1

RECONFIGURE WITH  OVERRIDE

GO

—Step 5 – Change database mode to emergency mode

 ALTER DATABASE AxRetailPOS SET  EMERGENCY

GO

SELECT * FROM  SYSDATABASES

 WHERE NAME = ‘AxRetailPOS’

 GO

— Update only one row in sysdatabase

BEGIN

  UPDATE sysdatabases

  SET status = 32768

  WHERE name = ‘AxRetailPOS’

 COMMIT TRAN

END

select * from  sysdatabases

 go

— Find out the database id

select name, filename from sysdatabases

 where dbid = ‘7’

 go

 ALTER DATABASE AxRetailPOS Rebuild Log

ON (NAME = N’AxRetailPOS_Log’,

   FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.SBHDB01\MSSQL\DATA\AxRetailPOS.ldf’)

GO

ALTER DATABASE AxRetailPOS Set  online

go

 DBCC rebuild_log (‘AxRetailPOS’, ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SBHDB01\MSSQL\DATA\AxRetailPOS_log.ldf’)

 GO