SQLTeam.com | Weblogs | Forums

Problems with transaction logs back up

Hi All

I am currently having problems with a job I set up to back up transaction logs. When I first set the job up, it work successfully. Then it started failing when I changed the database recovery model to simple from full. So I reset it back to full, hoping that the job will start running again, but it is still failing. Below is the failure report.

master.dbo.xp_create_subdir N'F:\Transacti...".: 100% complete End Progress Error: 2016-12-08 07:58:47.27 Code: 0xC002F210 Source: Back Up Database (Transaction Log) Execute SQL Task Description: Executing the query "BACKUP LOG [CareNotesLive] TO DISK = N'F:\Transac..." failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 07:58:46 Finished: 07:58:47 Elapsed: 1.217 seconds. The package execution failed. The step failed.

Take a FULL backup of the database and then Log backups will resume. They need an initial backup to "operate from" (if that makes sense)

Also if the recovery model is simple there is no need to back up the log as it should be minimal. Yes everything gets logged, however, when the transaction is successful then the log is no longer needed with simple recovery model.

But note that with SIMPLE Recovery Model your disaster recovery will only be as good as whenever you last made a Full Backup, and you will have no chance to recover any "current data", whereas with FULL Recovery Model you can play-forwards all the Log backups you have made and, in many circumstances, you can take a final "tail log backup",. before starting the restore, which will mean [if successful] that you have zero data loss.

1 Like

Simple: In Simple recovery model,you can not take log backups,so if a failure occurs,you only can restore from backup,that means you have data loss of your current work . So to answer your question,you dont have point in time restore capability,but you can restore database.

Full recovery model: Assume the same failure happens at 8 AM,since you have log backups every 5 minutes,you will be able to restore right upto the point of failure by taking tail log backup .

Simple Recovery model does not support Transaction log backup