**If you have a database that is set to full mode but does not have Transaction-log backups you need to create or take Transaction-log backups on a regular interval. If you do not perform a Transaction-log backup the log file will grow uncontrollably until you run out of disk.**
FULL Recovery Model:
This model provides complete backup and recovery of the database, allowing you to restore the database to a specific point in time. With the FULL recovery model, you must perform both full and transaction log backups. Full backups capture the entire database, while transaction log backups capture all the changes made to the database since the last log backup. This model provides the most complete data recovery capability, but it also requires more disk space and more frequent backups.
SIMPLE Recovery Model:
This model provides the simplest backup and recovery strategy, allowing you to recover the database only to the last full or differential backup. In the SIMPLE recovery model, you only need to perform full or differential backups, and transaction log backups are not required. The transaction log is truncated automatically, which means that the log space is reused as new transactions are written to the log. This model provides less data recovery capability than the FULL recovery model, but it requires less disk space and fewer backups.
To summarize, the FULL recovery model requires both full and transaction log backups, while the SIMPLE recovery model only requires full or differential backups. The choice between the two models depends on the business requirements for data recovery, the frequency of changes made to the database, and the available disk space for backup storage.
Full Recovery Mode - T-Log Details
In SQL Server, a log backup is a type of backup that captures all the transactions and modifications made to a database since the last log backup or the creation of the database. It essentially takes a snapshot of the transaction log, which records all the changes made to the database.
Log backups are incremental backups and are designed to work in conjunction with full or differential backups. Full backups capture the entire database, while differential backups capture the changes since the last full backup. Log backups capture the changes since the last log backup or database creation.
The primary purpose of log backups is to enable point-in-time recovery of a database. This means that you can restore a database to a specific point in time by restoring a full backup, any applicable differential backups, and one or more log backups up to the point in time you want to restore.
Log backups can be scheduled to run at regular intervals, such as every 10 minutes, every hour, or once a day, depending on the transaction volume of the database and the acceptable data loss in case of a failure. In addition, log backups can be compressed and encrypted for security and space-saving purposes.
How To Perform A Log Backup
To perform a log backup in T-SQL, you can use the BACKUP LOG statement. Here is the basic syntax:
BACKUP LOG database_name
TO backup_device [ ,...n ][ WITH options ]Here is a brief explanation of each element of the syntax:
BACKUP LOG specifies that you want to perform a log backup.
database_name is the name of the database you want to back up.
TO backup_device specifies the location where you want to store the backup. You can use a disk, tape, or other backup device.
[ ,...n ] specifies additional backup devices, separated by commas.
WITH options specifies additional options for the backup. Some commonly used options include INIT (to initialize the backup device), SKIP (to skip over any log records that have already been backed up), and NO_TRUNCATE (to prevent the transaction log from being truncated after the backup is complete).
To write a log backup with a file name of "DatabaseName-date-hours-minutes" in T-SQL, you can use the following code:
BACKUP LOG [DatabaseName]
TO DISK = 'C:\Backups\DatabaseName-' + CONVERT(VARCHAR(20), GETDATE(), 112) + '-' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '') + '.trn'In this code, the BACKUP LOG statement is used to initiate a log backup for the specified database (DatabaseName). The TO DISK clause specifies the file path and name for the backup file.
The CONVERT function is used to format the current date and time into the desired format. The GETDATE() function retrieves the current date and time, and the 112 and 108 arguments are used to specify the date and time formats, respectively.
The REPLACE function is used to remove the colons from the time portion of the date/time string, as colons are not allowed in file names.
Note that you should replace C:\Backups with the actual path where you want to store your backups.
Example To Loop Though Each DB and Take A Log Backup
To backup the logs for every database on the server, you can use the sp_MSforeachdb stored procedure along with the BACKUP LOG statement.
Here is an example T-SQL script:
EXECUTE master.sys.sp_MSforeachdb
'IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')
BEGIN
DECLARE @backupFileName NVARCHAR(500)
SET @backupFileName = N''C:\SQLBackups\' + REPLACE(''?'', '', '') + N'_LogBackup_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '''', '''') + N'.bak''
BACKUP LOG [?] TO DISK = @backupFileName