KB ID : 84
Published : Jan. 8, 2016
Created : Jan. 8, 2016
Last Modified : April 8, 2016

Cause

Error - “Msg 4208, Level 16, State 1, Server [servername], Line 1 The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. Msg 3013, Level 16, State 1, Server [servername], Line 1 BACKUP LOG is terminating abnormally”.

Solution

In order avoid the above issues in your environment, we recommend you to follow backup strategies with MSSQL server plugin in Vembu NetworkBackup & OnlineBackup clients.

a) Configure a separate backup job for ‘Master’ database with ‘FULL’ backup schedule alone, as ‘DIFFERENTIAL and TRANSACTIONAL’ log backups cannot be done on master database. Also, ‘master’ has to be restored first when restoring SQL Server for disaster recovery.

b) The databases with ‘Recovery Model’ as ‘Simple’ should be configured with ‘FULL’ backup type and optionally with ‘DIFFERENTIAL Log’ backup types as ‘TRANSACTIONAL’ log backups cannot be done.

c) The databases with ‘Recovery Model’ as ‘Full’ or ‘Bulk Logged’ should be configured with ‘FULL’ backup type and optionally with ‘DIFFERENTIAL’ and ‘TRANSACTIONAL LOG’ backup.

Also, we recommend our customers to do monthly/weekly Full backup and weekly/daily Differential backup and daily Transaction log backup for SQL Server database(s) depending on the potential of the SQL Server data.

The best method is to perform full backups as often as possible depending on the size of your database, along with differential backups and lastly with a combination of transaction log backups. The frequency is dependent on your environment and the acceptable data loss for your company. If you have extremely large databases it will be difficult to execute frequent full backups, so you need to look at a combination of options.

A good starting point might be the following backup schedule:

  • Execute a full database backup on a monthly basis.
  • Perform Differential backup on a weekly basis.
  • Perform transaction log backups on daily basis. Transaction log backups are the solution to meet a finer level of granularity for recovery purposes.

The differential backups will minimize the number of transaction log restores needed.

If the MSSQL Server differential backups are configured more frequently [Run every few hours] in Vembu NetworkBackup & OnlineBackup SQL Server backup and the MSSQL Server databases are changing rapidly, the clients backup space usage will grow faster. To avoid this, you can specify the maximum number of differential backups to be kept in the server. If the number of differential backup count exceeds this count, then the oldest differential backup file and the transactions logs taken between that differential backup and its successive differential/full backup job are deleted. The default value is 5. In differential backup, Vembu NetworkBackup & OnlineBackup client will take only the differences that have been modified after the last full backup, and this changes alone will be uploaded to the backup server. Hence it’s not necessary to do a full backup every time.

Transaction logs are logs of all the transactions that happened since the last backup – whether full or differential. In Transaction log, backup creates a copy of the active transaction log which lists any transactions that have occurred since the last backup. If you have configured log backup alone, then it will backup the changes since the last backup. The log backups will be automatically purged when the next Full/Differential backup schedule takes place.

edit retag flag offensive close merge delete