Sunday, 30 July 2017

How to create a transaction log backup in SQL server

Any transactions that happen in the database with respect to SQL server from the login to the select into statements are stored in an integral part of SQL server called “the transaction log”. A transaction log backup is the current situation of the database irrespective of having a full backups or differential backups. Having log backups along with the full backups is highly recommended in SQL server. Usually log backups are taken every 15 minutes to recover the database from a special point of time in case the database is deleted. Ideally, any database will consist of one .ldf file (logical file). However, having multiple log files will never improve performance of a database.

T-SQL Command to take transaction log backup:

BACKUP LOG [SQL_TEST] TO  DISK = N'C:\SQLArena\LOG\SQL_TEST.trn'
STATS = 10

Steps to follow in SQL Server Management Studio graphic user interface:
  • Right click on the “database” name
  • Click the option “Tasks”
  • A new window pops with a drop down to select “Transaction log”
  • Select the “Transaction log” option with a specific disk under option “Back up to”
  •  Click “OK” to take the transaction log backup
The screenshot for the same is mentioned below:





0 comments: