WWW.TEXALAB.COM
Restoring SQL Server Database after Accidental Deletion of .mdf
File
SQL Server 2012
www.texalab.com 08/06/2016
Restoring SQL Server 2012 Database after Deletion of .mdf File
Sometime accidently 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
Creation of Database with SQL script
CREATE DATABASE [StudentInfo] CONTAINMENT = NONE
ON PRIMARY
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)"
Creation of Table with SQL script
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
Run the script for populating the data to StudMast table insert from rollno 1000 to 2000
Declare @ctrl int set @ctrl=1000 while @ctrl<=2000 begin
insert into StudMast values(@ctrl,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA') set @ctrl=@ctrl+1
5. Take a complete backup of StudentInfo Database with name StudentInfoFullBackup.bak
Run the script for taking full backup of StudentInfo Database
BACKUP DATABASE [StudentInfo] TO DISK = N'D:\RestoreDemo\StudentInfoFullBackup.bak'
Run the script for taking Transactional Log backup of StudentInfo Database
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
12. Stop SQL server from windows services as shown.
12.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 backup captures 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
18. Restore the Transaction Log "StudInfoLog4" the tail log from "D:\RestoreDemo" with option "Restore with Recovery"
