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

 
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

 
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

 
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

Database

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

 
Step 7: Verify the Database and file group
 

Database - Restored

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

USE AdventureWorks
EXEC sp_helpfile

 

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.