Sep 6, 2012

SQL Server Error: 5132 ,Error: 17207 when attach DB

SQL Server Error: 5132 , Error: 17207, Severity: 16
Loss SQL Server 2005 .LDF file , when try to attach MDF/NDF facing problem.
Step 0) Copy MDF/LDF to another location for backup purpose.
Step 1) Create DB with same DB/MDF/NDF/LDF Name
Step 2) Stop SQL Service
Step 3) Replace new DB MDF/NDF file with old MDF/NDF file
Step 4) Start SQL Server
Step 5) SQL Server 2005

ALTER DATABASE  YourDB SET EMERGENCY
go
select * from sysdatabases
go
ALTER DATABASE YourDB SET SINGLE_USER
go
DBCC CHECKDB(YourDB, REPAIR_ALLOW_DATA_LOSS)
go
ALTER DATABASE SFC SET MULTI_USER

CheckDB complained about the missing log then went and rebuilt the log 


#Remark - SQL Server 2000 update system tables
EXEC sp_configure 'allow updates'1
RECONFIGURE WITH OVERRIDE;
 
-- do some modification to system tables
 -- Ex.Put the DB in suspect mode:
 -- update sysdatabases set status = 32768 where name ='YourDB'
 
EXEC sp_configure 'allow updates'0
RECONFIGURE WITH OVERRIDE;