Transaction log backup error

JulieShop1

I've created 2 new jobs in Microsoft SQL Management Studio - one for full backup of database, and another - for backup of tran logs. The full backup is scheduled to run once every morning, and it runs just fine. However, tran log backup, which is scheduled to run every hour, has some problems. Sometimes it runs successfully 3-4 times and failing after that with this error:

Executing the query "BACKUP LOG [survey_p0037832] TO DISK = N'G:\\database backups\\logs\\survey_p0037832_backup_200708021000.trn' WITH NOFORMAT, NOINIT, NAME = N'survey_p0037832_backup_20070802100002', SKIP, REWIND, NOUNLOAD, STATS = 10

" 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.

After I execute the full database backup on demand, then tran logs backup is runnung fine again for next 3-4 times... We do not have any conflict in time, I avoided that scenario... Do you know how can I fix it Thank you

oj

Transaction log backup requires a full backup to base it on. You must ensure the database is set to Full recovery and the database backup is a full backup. Also, you must not reset the database recovery to Simple after a full backup (and before the log backup). Doing so will reset the status needed for the log backup.

Madhu K Nair

has this database in Database mirroring

Madhu

Peter Byrne MSFT

What version of SQL is this It sounds like something is breaking the log backup chain by either issuing a BACKUP LOG WITH TRUNCATE_ONLY or setting the recovery mode to SIMPLE via ALTER DATABASE. There should be errorlog entries to indicate if these occurred with SQL 2005.

JulieShop1

The server SQL Server 2005 is comprised of two servers. Both machines utilize RAID 1, or disk mirroring, if this is what you're asking me about.

Besides, I do not think that databases are configured for mirroring.

JulieShop1

Hello,

I have this backup scheduled for "All databases" on server, which is around 300 at this point. The application is set up that way that it creates a new database as needed (dynamically). Do not ask me why - I hate this design... So, I think what happens is since my full backup of all databases are scheduled to run only once at night, and tran logs every hour, when new database is created during the day, there is no full backup for it, that is why tran logs backup fails. Becuase after the failure, if I run full backup again, then tran log runs just fine afterwards.

I am new to MS SQL Server, I am mostly working with Sybase IQ. Do you know if I can "trigger" full backup every time when new database created to avoid tran lof failure

Thank you very much

Sue Hoegemeier

Yes...that would likely be why you are getting the error - the scenario where the new database is created, the transaction log backup happens before the first full database backup of the new database.

I haven't tested this but I would look at a server scoped DDL trigger. The application end should also be able to manage this - if it's dynamically creating the database, it could also do a full backup after the database is created. Either way, You could still run into some timing issues though unless you first check for a full database backup when doing the log backups. That would be another option - check if a full backup has been done when doing the log backups. And a lot of other options - such as create the transaction log backup list (store in a table) from the database full backups.

-Sue