Home > SQL Server > SQL – Database restore using mdf file

SQL – Database restore using mdf file

Recently, one of my team member asked me to restore a database by providing only mdf file. This restoring process is really interesting and simple process as it is not using .bak file.

I am simulating this process using AdventureWorks Database.

Step 1: Database backup – In case of any disaster process as I wouldn’t take any chance of loss of data.

USE AdventureWorks
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.bak'
GO

 

Database Backup

Database Backup


 
Step 2: Analysis of file filegroup using sp_helpfile

USE AdventureWorks
EXEC sp_helpfile

 
As we could see, primary and log files and its size (size of log file need to consider)
 

Database files

Database files


 
Step 3: Stop SQL Server services to copy the mdf file of database as we couldn’t take a copy of mdf file while services are running.
 
Stop - SQL Services

Stop - SQL Services


 
Step 4: I have just copy the mdf file to local system folder.
 
Step 5: Start SQL Server services and delete the existing AdventureWorks Database
 
Running - SQL Services

Running - SQL Services

Database

Database

Database - Delete

Database - Delete


 
Step 6: Restoring Database using mdf file as
 

USE [master]

CREATE DATABASE AdventureWorks ON
 (FILENAME = N'C:\Users\prav\Documents\BIpassion\SQL - Restore\AdventureWorks_Data.mdf')
 FOR ATTACH_REBUILD_LOG
 GO

 

Database Restore

Database Restore


 
Step 7: Verify the Database and file group
 
Database - Restored

Database - Restored


 
Step 8: Verifying the filegroup of restored database (ldf file created)

USE AdventureWorks
EXEC sp_helpfile

 

Created ldf file

Created ldf file


 
Points to consider:
 
1. Don’t recommend to do this approach on production without having disaster recovery plan.
2. Need to consider the filegroup files, if we have mdf and many ndf files the system may not restored so we may need to move data from ndf file to mdf file.
3. There are many other approaches to solve this issue as it is my personal approach to implement this process.

Advertisements
Categories: SQL Server Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: