Backup Part-5

What all operations are minimally logged when the database is in Bulk Logged Recovery mode?

• Bulk import operations (bcp, BULK INSERT, and INSERT… SELECT). For more information about when bulk import into a table is minimally logged.
• SELECT INTO operations.
• Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data. Note that minimal logging is not used when existing values are updated.
• WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns. Note that minimal logging is not used when existing values are updated.
• CREATE INDEX operations (including indexed views).
• ALTER INDEX REBUILD or DBCC DBREINDEX operations.
• DROP INDEX new heap rebuild (if applicable).

Is there any option to prevent All successful SQL Server backup entries from writing to the SQL Server Error Log?

Yes – We can enable the trace flag 3226.

What is LSN ?

Every record in the SQL Server transaction log is uniquely identified by a Log Sequence Number (LSN).

The LSNs are sequential and in the particular ordered, new LSN is always higher than the old LSN.

What is the value of DatabasebackupLSN for the first full backup file?

A DatabaseBackupLSN is a zero and FirstLSN is same as CheckpointLSN.

What is First LSN?

The log sequence number (LSN) of the first transaction in the backup set. Blank for file backups.

What is Last LSN?

The log sequence number (LSN) of the last transaction in the backup set. Blank for file backups.

What is Full LSN?

The log sequence number (LSN) of the most recent full database backup.

How to find LSN information of the Database Backup Files?

SELECT last_log_backup_lsn

FROM sys.database_recovery_status WHERE database_id = DB_ID();

Please visit other related articles...

https://sqldbaiq.blogspot.com/p/backup-iq.html

https://sqldbaiq.blogspot.com/p/backuprestore-part-2.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-3.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-4.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-5.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-6.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-7.html

https://sqldbaiq.blogspot.com/p/backuprestore-part-8.html

References: Thanks to the all the SQL Server bloggers who wrote and shared the valuable information on their blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.