BRIEF INSTRUCTION ABOUT MDF,LDF

By :MD MASUM REZA

AGENDA OF THE DAY

Introduction What is MDF , LDF and NDF Locating .mdf File .ldf file in server Role of MDF and LDF Restore data after truncating the

table(with example) Differential Backup Full Backup Conclusion

Introduction :-

The SQL Server provides the facility to restore your database by attaching your .mdf file and .ldf file to the database.

We can directly Attach our existing .mdf and .ldf file to an SQL instance by using SQL Server Management Studio or T-SQL.

We can restore our .mdf and .ldf file through execute some query.

What is MDF , LDF and NDF : -

MDF :–Stands for Master Database File. It contains all the main information of the database that are part of the server. This extension also points to various other files. It plays a crucial role in information storage. Overall it is very important for safe and secure supervision of data. In case this file gets damaged, an MDF recovery procedure is conducted to recover it. Doing so is important in order to save the data from going missing.

NDF :-stand for Next Data File . NDF file is a user defined secondary database file of Microsoft SQL Server with an extension .ndf, which store user data. Moreover, when the size of the database file growing automatically from its specified size, you can use .ndf file for extra storage and the .ndf file could be stored on a separate disk drive. Every NDF file uses the same filename as its corresponding MDF file. We cannot open an .ndf file in SQL Server Without attaching its associated .mdf file.

LDF :–Stand for LOG Database File .This file stores information related to transaction logs for main data file. It basically keeps track of what all changes have been made in the database. The information that this file stores ranges from date/time of change, details of the changes made, as well as information related to whoever made the changes. Information related to computer terminals where changes took place is also stored in the logs.

Locating .mdf File .ldf file in server :-

Within each database, you will find two files namely MDF and LDF. These two are basically file extensions used in Microsoft SQL. These files get

automatically created at the time of database creation. They also share the same storage location. The reason why these files are so important is because they happen to be part of backup and recovery process. In simpler words, in case something bad happens to the database, these are the files the administrator will resort to for restoring and recovering the lost/damaged data.

Query can Execute: select * from sys.database_files

Role of MDF and LDF :- select * from fn_dblog (null , null)

Role of MDF and LDF

The primary purpose of an LDF file is to provide the ACID concept – Atomicity, Consistency, Isolation, and DurabilityAtomicity: if one part of the transaction fails, the entire transaction fails, and the database state is left unchangedConsistency: any transaction brings the database from one valid state to anotherIsolation: the execution of concurrent transactions brings the database to a state as if the transactions were executed serially, one by oneDurability: once committed, the transaction remain so, even in the case of errors, power loss, or crashes

An LDF file stores enough information to replay or undo a change, or recover the database to a specific point in time. Therefore, due to various auditing or recovery requirements, there is often a need to open the LDF file and view its contents. But viewing LDF file content is not an easy task

Restore data after truncating the table :-The below example will show how can retrieve data after truncate or delete if happen :

use master1 .create database Test go

USE Test GO CREATE TABLE Student (     StudentID BIGINT IDENTITY PRIMARY KEY,     StudentName VARCHAR(128),     RollNo VARCHAR(10) ) GO INSERT INTO Student(StudentName , RollNo) VALUES ('Reza','101') ,('Hari','102') ,('Sunil','103') ,('Naveen','104') GO

Restore data after truncating the table :-

3. Take Database Backup : BACKUP DATABASE Test TO DISK = 'D:\Backup\New folder\MyTestDB.BAK' GO

4. Truncate table student

5. select * from Student

2. select * from Student

Restore data after truncating the table :-

6. SELECT [Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name] FROM fn_dblog (NULL, NULL) where [Transaction Name] ='TRUNCATE TABLE‘

7. 00000021:0000005d:0001 take LSN (Log Sequence Numbers) from above code.

8. Convert LSN number from HEX to Decimal number. like below code

SELECT CAST (CONVERT (VARBINARY,'0x'+'00000021', 1) AS INT) as FirstPart, --33CAST (CONVERT (VARBINARY,'0x'+'0000005d', 1) AS INT) as SecondPart, --93CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as ThirdPart --1GO

Restore data after truncating the table :-

9. Add preceding zeros for 93and 1. Note : no need for 33 93=0000000093 (8 zeroes) total should be 10 digits 1 =00001 (4 zeroes) total should be 5 digits. => 33000000009300001 Now do following steps one by one .

BACKUP LOG TestTO DISK = 'D:\Backup\New folder\MyTestDB.TRN'   GO

RESTORE DATABASE MyTestDB_Copy       FROM DISK = 'D:\Backup\New folder\MyTestDB.bak'   WITH       MOVE 'Test' TO 'D:\Backup\New folder\MyTestDB.mdf',       MOVE 'Test_log' TO 'D:\Backup\New folder\MyTestDB_log.ldf',       NORECOVERY           GO 

Restore data after truncating the table :-

RESTORE LOG MyTestDB_Copy   FROM       DISK = N'D:\Backup\New folder\MyTestDB.TRN'   WITH       STOPBEFOREMARK = 'LSN:33000000009300001’

10 . use MyTestDB_Copyselect * from Student

Differential Backup:-A differential backup is created similarly to a full backup, but with one important difference – the differential backup only contains the data that has changed since the last full backup (the active portion of the transaction log). Differential backups are cumulative not incremental. This means that a differential backup contains all changes made since the last full backup, in spite of the fact that they have already been included in previous differential backups. Differential backups are created the following way:

How to Make a Differential BackupTo make a differential database backup simply add “WITH DIFFERENTIAL” clause:BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL

Full Backup :-

The simplest kind of SQL Server backup is a full database backup. It provides a complete copy of the database but allows restoring the database only to a point-in-time when the backup was made.Even if you add the “WITH STOPAT=<time or log sequence number>” option to restore command you will not get the expected result because this option applies only when you restore the transaction log. Please see how a periodic full backup works in the picture below:

How to Make a Full BackupTo make a full backup you can use T-SQL command:BACKUP DATABASE your_database TO DISK = 'full.bak'

Another way to backup a database is to use SQL Server Management Studio (SSMS): right click on the database you want to backup, select “Tasks”, then “Back up…”. Choose “Full” backup type, add a backup destination and click “OK”.

Conclusion

In the above section, we learned how to restore LDF and MDF files to a database. It is highly recommended that you have to detach the MDF file before attaching a new one. This is a very helpful method to restore database using MDF and LDF file.

1. https://sqljumble.blogspot.in/2016/11/how-to-recover-data-after-truncate.html

2. https://sqlbak.com/blog/recover-deleted-data-in-sql-server/

3. https://sqlbak.com/academy

Thanking You