Recovering Deleted Data in SQL Server Databases

How many times you have come across unexpected data deletion in the production environment as looking for data costly tools, to recover your data. If you cannot recover your data, there can be situations where you will be thrown out of the business. 

How do you plan for these accidental or deliberate data deletions? Point in Time Recovery with SQL Server is the option that allows you to recover the deleted data. However, you need to better understanding SQL Server Recovery Models and Transaction Log Use in order to enable Point in Time Recovery. 

This is an important configuration that needs to be done and no point complaining later. 

Log File in SQL Server Database

A logfile is an important component in a Database. The log file is part of a lot of operations in a database such as Writing to the Database, Transactions, Recovery. Further, there are important features that can be utilized with log files such as Replication, Mirroring, and Log Shipping. 

Due to the many usages of the Transaction Log file, it is important to understand the behaviour of the transaction log file. The transaction log behaviour will be determined by the Recover Models which are discussed in this article.

The Transaction Log file is extremely useful to handle Point in Time Recovery in live or production environments to recover from accidental data deletes and other operations. This article describes how to configure point in time recovery. Further, log shipping can be configured to enable data disaster recovery.  

In the later version of SQL Server, provides the mechanism to customized Transaction Log Backups. 

Customizing Differential and Transaction Log backups

source: NOVAbackup

In SQL Server, we used Full, Differential and Log backups to support various needs of the database administrators. However, we used to take these backups in a defined frequency without considering the data volume of each backup. 

This article discusses how to customize your backups in order to achieve better maintenance plans for database backups.

Customized Transaction Log Backups

Transaction Log backups are important in a Production environment. It will make sure that you manage your log file size and keeping backups in case of a need to restore.

I am pretty much sure, most of you have scheduled transaction log backups. If you have scheduled Transaction log backups every 15 minutes, then you will see four log backups every hour and will result in nearly 100 backup files a day and you are looking at around 700 log backups per day. Unlike differential backups, you need all your lob backups to recover. Sometimes, you might have less or no transactions but still, there will be a log backup. 

Now the question is, Can we create transaction log backup when there is sufficient size. Yes, you can if you are running SQL Server 2017 or later. 

In sys.dm_db_log_stats Dynamic Management Function (DMF), there is a new column called log_since_last_log_backup_mb tells you what is the log file size after the last log backup. 

Using the following script, you can perform transaction log backups when the log file size is more than a specific size. 

DECLARE @log_since_last_log_backup_mb NUMERIC(9, 2)
DECLARE @ThreasholdSize INT = 25
DECLARE @folderName VARCHAR(30) = 'D:\DBBACKUP'
DECLARE @DatabaseName VARCHAR(30) = 'LB1'

SELECT @log_since_last_log_backup_mb = log_since_last_log_backup_mb
FROM sys.dm_db_log_stats(db_id(@DatabaseName))

IF @log_since_last_log_backup_mb > @ThreasholdSize
BEGIN
   DECLARE @fileName NVARCHAR(400) = @folderName + '\' + 

   @DatabaseName + SUBSTRING(REPLACE(CONVERT(VARCHAR, GETDATE(), 111), '/', '') 

   + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', ''), 0, 13) + '.bak'

	BACKUP LOG [LB1] TO DISK = @fileName
	WITH NOFORMAT
		,NOINIT
		,SKIP
		,NOREWIND
		,NOUNLOAD
		,STATS = 10
END
ELSE
	PRINT 'No BACKUP'