Nest Software

Why Model DB:

The Model database is the template database that SQL Server uses to create new databases. Each time you create a new database in SQL Server, the contents of the Model database are copied to the new database to establish its default objects, including tables, stored procedures, and other database objects. The Model database is required even if you do not intend to create any new user databases. Each time SQL Server starts, the TempDB is recreated using the Model database as its template. By default, the Model database is empty when it is created.
Nest Software

Why Master DB:

The Master database is the primary configuration database in SQL Server. It contains information on all the databases that exist on the server, including the physical database files and their locations. The Master database also contains SQL Server’s configuration settings and login account information. The following list outlines the information contained in the master database:

■ Server Registrations and Remote Logins
■ Local Databases and Database Files
■ Login Accounts
■ Processes and Locks
■ Server Configuration Settings

Given its importance to SQL Server, a current backup of the Master database is critical to any server recovery.

MS Sql Server Read Log file

First of all, in order to be able to read any meaningful data your database needs to be in full recovery or bulk-logged mode. Otherwise you probably won't find much there.

Select * from fn_dblog(null,null)

RowLog Contents 0 to RowLog Contents 5 store all data

Ex: The data in RowLog Contents 0 is:

0x300008000100000002000001001200526564

Which can be broken down like this:

30 Status bit A
00 Status bit B
0800 Offset to find the number of columns in the row.
01000000 Data of fixed length col = 1
0200 Number of columns
00 Null bitmap
0100 Number of variable length columns
1200 Position where first variable length column ends, this is byte swapped which is 0x0012 which translates to 18.
526564 Data in variable length column like 'Dipankar Banik'

Run DML commands to see what is captured in SQL Server transaction log:
SELECT
[Current LSN],
[Transaction ID],
[Operation],
[Transaction Name],
[CONTEXT],
[AllocUnitName],
[Page ID],
[Slot ID],
[Begin Time],
[End Time],
[Number of Locks],
[Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN
('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')

Finding internal SQL Server operations in transaction log:

--Get how many times page split occurs.
SELECT
[Current LSN],
[Transaction ID],
[Operation],
[Transaction Name],
[CONTEXT],
[AllocUnitName],
[Page ID],
[Slot ID],
[Begin Time],
[End Time],
[Number of Locks],
[Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Transaction Name]='SplitPage'
GO

--Get what all steps SQL Server performs during a single Page Split occurrence.
SELECT
[Current LSN],
[Transaction ID],
[Operation],
[Transaction Name],
[CONTEXT],
[AllocUnitName],
[Page ID],
[Slot ID],
[Begin Time],
[End Time],
[Number of Locks],
[Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Transaction ID]='any transaction id'