Archive

Posts Tagged ‘SQL – Restore’

SQL – Database restore using mdf file

August 12, 2011 Leave a comment

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: