Restore SQL Database from MDF file without LDF

Leave a comment (0) Go to comments

Today I received a call from a developer and he asked me “Can we recover a database only with a data (.mdf) file, if yes then How to do that ?

I said YES, we can recover a database with a data file too, we can easily do that it’s pretty straight forward, instantly I written a small code for him to describe, how to do that.

In the below script I have created the database, create a table in that, dropped its log file and created the database with the .mdf file.

-- created database with .mdf and .ldf file
CREATE DATABASE [TEST] 
ON  PRIMARY 
( NAME = N'TEST', FILENAME = N'C:\TEST_Data.mdf')
 LOG ON 
( NAME = N'TEST_log', FILENAME = N'C:\TEST_log.ldf')
GO

-- inserting data into database
use TEST
go
CREATE TABLE customer
(    customer_id int not null,
     customer_name    varchar(50)    not null,
     address    varchar(50),    
     city    varchar(50),    
     state    varchar(25),    
     zip_code    varchar(10),    
)    

-- inserting records
insert into customer values(1,'John','9290-9300 Transit Road','Amherst','NY','14051')
insert into customer values(2,'Sam','4030 Maple Ave.','Amherst','NY','14051')
insert into customer values(3,'Jason','4888 State Route 30','Amherst','NY','14051')
insert into customer values(4,'Joe','1651 Clark Street','Amherst','NY','14051')
go

-- Selecting Data and verifying Data is inserted
select * from TEST..customer 

-- deleting the log file
-- detaching the database file
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TEST'
GO

-- now next step is delete the file manually or you can do it from command prompt
EXEC xp_cmdshell 'del C:\Test_log.ldf'

EXEC xp_cmdshell 'dir C:\Test_log.ldf'
-- script to attach the database 
USE [master]
GO
CREATE DATABASE TEST ON 
( FILENAME = N'C:\TEST_Data.mdf' )
FOR ATTACH
 GO 

TAG : Restoring SQL Server databases from .mdf file, attach database from mdf file, recover SQL server database from a mdf / data file only, How to recover database from MDF in SQL Server,How to attach a MS SQL database (.mdf) file with a missing log file


EOF - Restore SQL Database from MDF file without LDF, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.