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
