Chapter-1 Restoring SQL Server Database after Accidental Deletion of .mdf File in SQL Server 2012

The document covers how to restore the database if .mdf file of SQL Server 2012 Database is accidentally deleted or corrupted. The document covers step by step procedures with appropriate images on restoration activities.

Download Tutorial for Restoring SQL Server 2012 Database after Accidental delete or corruption of MDF file

Restoring SQL Server 2012 Database after Deletion of .mdf File

Sometime accidentally deletion or corruption of SQL Server Database(.mdf) may take please. In such case if we had taken some old full backup and Transaction log backup time to time we can restore the complete database with the help of these files.

So for demo purpose here I am going to create a database , insert some records , take full backup then insert records taken transaction log backup and iterate the steps of insert and transaction log backup for 3-4 times. Then to explain the restoration of SQL Server 2012 Database after Deletion of .mdf File we may forcefully delete the .mdf file and then try to restore the database with Transaction Log backup.

Database Creation and filling of Data to table

1. Create a folder on your system drive say “D:\RestoreDemo” for keeping all your backup files.
2. Create a Database with name “StudentInfo” with SSMS as shown below

   

CREATE DATABASE [StudentInfo]
CONTAINMENT = NONE
ON  PRIMARY
( NAME = N’StudentInfo’, FILENAME = N’D:\RestoreDemo\StudentInfo.mdf’ , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON  ( NAME = N’StudentInfo_log’, FILENAME = N’D:\RestoreDemo\StudentInfo_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 1024KB)

3. Create a table “StudMast” with fileds “rollno int”, and “name varchar(100)”

create table StudMast (rollno int,name varchar(100))

4. Write while loop to fill the data for demo purpose in table StudMast say from rollno 1001 to 2000

SCRIPT:
Declare @ctrl int
set @ctrl=1000
while @ctrl<=2000
begin
insert into StudMast values(@ctrl,’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’)
set @ctrl=@ctrl+1
end

5. Take a complete backup of StudentInfo Database with name StudentInfoFullBackup.bak

BACKUP DATABASE [StudentInfo] TO  DISK = N’D:\RestoreDemo\StudentInfoFullBackup.bak’

6. Take Transaction log backup first time with name StudInfoLog1

BACKUP LOG [StudentInfo] TO  DISK = N’D:\RestoreDemo\StudInfoLog1′

7. Execute the loop again and insert from rollno 2001 to 3000

Run the script for populating the data to StudMast table insert from rollno 2001 to 3000 as given in step-4(Image)

Declare @ctrl int
set @ctrl=2001
while @ctrl<=3000
begin
insert into StudMast values(@ctrl,’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’)
set @ctrl=@ctrl+1
end

8. Take Transaction log backup first time with name StudInfoLog2 as given In step-6(Image)

Run the script for taking Transactional Log backup  of StudentInfo Database
BACKUP LOG [StudentInfo] TO  DISK = N’D:\RestoreDemo\StudInfoLog2′

9. Execute the loop again and insert from rollno 3001 to 4000 as given in step-6(Image)

Declare @ctrl int
set @ctrl=3001
while @ctrl<=4000
begin
insert into StudMast values(@ctrl,’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’)
set @ctrl=@ctrl+1
end
10.Take Transaction log backup first time with name StudInfoLog3 as given In step-6(Image)

Run the script for taking Transactional Log backup  of StudentInfo Database

BACKUP LOG [StudentInfo] TO  DISK = N’D:\RestoreDemo\StudInfoLog3′

11.Execute the loop again and insert from rollno 4001 to 5000 as given in step-4(Image)

Declare @ctrl int
set @ctrl=4001
while @ctrl<=5000
begin
insert into StudMast values(@ctrl,’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’)
set @ctrl=@ctrl+1
end

12. Stop SQL server from windows services as shown.

13. Go to the directory where MDF and LDF file of the database StudentInfo is created. In my case it is

“C:\Program Files\Microsoft SQL Server2012\MSSQL11.MSSQLSERVER2012\MSSQL\DATA”  and delete the mdf file and start the SQL Server Services again

Restoration of Database from Last full backup and consecutive TRANSACTION LOG Backups

13. Before starting the Restoration process it is good practice to take Transactional Log Backup of “StudentInfo” database with name “D:\RestoreDemo\StudInfoLog4″(tail log backup). A tail-log backupcaptures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.

Script

BACKUP LOG[StudentInfo] to Disk=N’D:\RestoreDemo\StudInfoLog4′ with continue_after_error

14. Restore the Last Taken full backup of “StudentInfoFullBackup.bak” with option “Restore with NoRecovery” with Replace option

15. Restore the Transaction Log “StudInfoLog1” from “D:\RestoreDemo” with option “Restore with NoRecovery” as it is given in figure-1,2,3

16. Restore the Transaction Log “StudInfoLog2” and “StudInfoLog3” from “D:\RestoreDemo” with option “Restore with NoRecovery” as done at step-15
17. Restore the Transaction Log “StudInfoLog4” the tail log from “D:\RestoreDemo” with option “Restore with Recovery”
18. Restoration of “StudentInfo” completed successfully , please go and see all the data is their or by checking StudMast table and counting the number of rows present on it.