Knowledge Base : Glossary : Suggest a Question
Search The Knowledge Base
Browse The Knowledge Base
Knowledge Base : General

Recover database from MDF file alone

1. ------- Detach Database
use master
go
sp_detach_db DBMAME
go

2. ------- Accidental Deletetion.
Delete DBNAME.ldf from application path Data files folder

3. ------- Try to attach DBNAME.MDF
sp_attach_db 'DBNAME','DB MDF FILE PATH WITH EXTENSION'

Error message:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DBNAME'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'DB LDF FILE PATH WITH EXTENSION' may be incorrect.
Device activation error. The physical file name 'DB LDF FILE PATH WITH EXTENSION' may be incorrect.

4. ------- Rename Database MDF File to New MDF File

5. ------- Create database Required DB Filename

USE master
GO
CREATE DATABASE DBNAME
ON PRIMARY ( NAME = DBNAME,FILENAME = 'DB MDF FILE PATH WITH EXTENSION',SIZE = 2MB,MAXSIZE = 10,FILEGROWTH = 1)
LOG ON ( NAME = DBNAME_Log,FILENAME = 'DB LDF FILE PATH WITH EXTENSION',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1)
GO

6. ------- Stop SQL Server

7. ------- Delete MDF File from Application path

8. ------- rename New MDF File to DBNAME.MDF

9. ------- Start SQL Server service

10. ------- run the following

use Master
go
sp_configure "allow", 1
go
reconfigure with override
go

11. -------
update sysdatabases set status = 32768 where name = 'DBNAME'
go
checkpoint
go
shutdown with nowait
go

12. ------- delete DBNAME.LDF

13. ------- run this query

dbcc traceon(3604)

14. ------- rebuild Log

dbcc rebuild_log('DBNAME','DB LDF FILE PATH WITH EXTENSION')

15. -------
update sysdatabases set status = 0 where name = 'DBNAME'

16. ------- restart sql server
17. ------- run the following query
use DBNAME
go
dbcc checkdb
go
dbcc checkalloc
go


=====
Thanks Sethu for this scripts - Techsupport Team

Related Articles
There are no related articles


Viewer Comments Add Comment
No viewers have posted comments

Powered By AutoKB