Step-by-step industry-standard process to restore a SQL Server database after
accidental deletion of .mdf and .ldf files
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
( NAME = N'StudentInfo', FILENAME = N'D:\RestoreDemo\[Link]' , 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)"
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
end
5. Take a complete backup of StudentInfo Database with name [Link]
Run the script for taking full backup of StudentInfo Database
BACKUP DATABASE [StudentInfo] TO DISK = N'D:\RestoreDemo\Backups\[Link]'
6. Take Transaction log backup first time with name StudInfoLog1
Run the script for taking Transactional Log backup of StudentInfo Database
BACKUP LOG [StudentInfo] TO DISK = N'D:\RestoreDemo\Backups\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\Backups\StudInfoLog2'
9. Execute the loop again and insert from rollno 3001 to 4000 as given in step-4(Image)
Declare @ctrl int
set @ctrl=3001
while @ctrl<=4000
begin
insert into StudMast values(@ctrl,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
set @ctrl=@ctrl+1
end
[Link] 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\Backups\StudInfoLog3'
[Link] 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
[Link] Transaction log backup first time with name StudInfoLog4 as given In step-6(Image)
It is good practice to take Transactional Log Backup of "StudentInfo" database with name
"D:\RestoreDemo\Backups\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.
13. Stop SQL server from windows services as shown.
[Link] 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
Restoration of Database from Last backup .mdf file and TRANSACTION LOG Backups
Preconditions for Point-in-Time Recovery
Before proceeding, ensure the following:
1. Database is in FULL recovery model
2. You have:
o 1 Full backup
o Sequential Transaction Log backups (no missing log)
3. The target recovery time falls within the log backup chain
4. No log backup in the chain is damaged or missing
Conceptual Flow (Very Important)
SQL Server replays log records in sequence:
1. Restore Full Backup → NORECOVERY
2. Restore Log Backup 1 → NORECOVERY
3. Restore Log Backup 2 → NORECOVERY
4. Restore Log Backup 3 → NORECOVERY
5. Restore Log Backup 4 → STOPAT = 'PointInTime' + RECOVERY
STOPAT is applied ONLY on the LAST log restore
SQL Server Point-in-Time Restore Script
Step 1: Restore Full Backup
RESTORE DATABASE StudentInfo FROM DISK = 'D:\RestoreDemo\StudentInfoFullBackup'
WITH NORECOVERY,STATS = 10;
GO
Step 2: Restore Transaction Log Backups Sequentially
RESTORE LOG StudentInfo
FROM DISK = 'D:\RestoreDemo\StudInfoLog1' WITH NORECOVERY;
GO
RESTORE LOG StudentInfo
FROM DISK = 'D:\RestoreDemo\StudInfoLog2' WITH NORECOVERY;
GO
RESTORE LOG StudentInfo
FROM DISK = 'D:\RestoreDemo\StudInfoLog3' WITH NORECOVERY;
GO
Step 3: Restore LAST Log Backup with STOPAT
RESTORE LOG StudentInfo
FROM DISK = 'D:\RestoreDemo\StudInfoLog4'
WITH STOPAT = '2026-01-18 10:52:00', RECOVERY,STATS = 10;
GO
Database is now restored exactly to 10:52:00
Key DBA Rules (Interview-Critical)
STOPAT cannot be used with NORECOVERY
STOPAT must be used only once, on the last log
If target time is in Log2, then:
o Restore Log1 → NORECOVERY
o Restore Log2 → STOPAT + RECOVERY
o Do not restore Log3 & Log4
Missing even one log backup breaks PITR