What are the challenges you face when working across database platforms? Take the survey

backup while running trn log backup

AlexGAlexG Posts: 24
edited May 2, 2007 12:50PM in SQL Backup Previous Versions
Hi ,

I have server with 7 databases
2 databases (db1 = 60 GB ,db2 =40 gb)
trn log backup running for db1 and db2 ebery 15 minutes
Mirroring set for db1 and db2

I created job to backup all db at 11:00 pm

@db_name sysname ,
@sql varchar(8000)
declare C1 cursor for
select name from sysdatabases
dbid not in(2,3) --all exepet temp and model

open c1
fetch c1 into @db_name

@fetch_status = 0

set @sql = 'master..sqlbackup N''-SQL "BACKUP DATABASE TO DISK = ''''\\myserver\backup\FULL_sql001_'+@db_name+'.sqb'''' WITH NAME = ''''<AUTO>'''', DESCRIPTION = ''''<AUTO>'''', INIT, COMPRESSION = 3"'''

select @sql
exec (@sql)
fetch c1 into @db_name
close c1
deallocate c1

Job start at 11 pm ,run without errors ,but backup for db1 and db2 not created.
No errors in sql server log

at 11 pm tran log backup started for db1,it run for 17 minutes and generated 200 mb log file

When restart job manualy in morning all backup created

1. Could it be resource problem ?


  • Options
    Eddie DEddie D Posts: 1,792 Rose Gold 5

    Thank you for sending in the requested SQL Backup Log files.

    Using SQL Backup and you have created two scheduled backup jobs to occur or trigger at the same time. One of the jobs will gain access to the database and prevent the other job obtaining access to the database.

    Unlike native Microsoft SQL Backup, which gets around this issue by queuing up the jobs, unfortunately our software will not queue the jobs. In the syntax you supplied, SQL Backup will try the next database in the list.

    To solve this problem, I recommend that you hold off the log backups for the period of the full backup. For example the full backup takes 2 hours to complete and you perform log backups every 15 minutes at 00, 15, 30 & 45 minutes past each hour. Edit the scheduling of the log backups so that the last backup is at 10:45 and to restart at 01:15.

    I am surprised as to why no error message or SQL Backup Log file is not generated. I questioned the SQL Backup development team on this matter, their answer to me was they were unsure if an error is generated in Version 4. They have assured me that an error should be generated in the new Version 5 which was released yesterday.

    Also I notice that your backup script generates a SQL Backup log file for each database. Normally when using the following syntax which is BACKUP DATABASES, this creates a single SQL Backup Log file. Below is an example syntax:

    -SQL "BACKUP DATABASES [db1, db2, master, msdb, AdminDB, ReportingDB, ReportingTempDB] TO DISK = 'C:\Backups\<AUTO>' "

    Using the BACKUP DATABASES syntax will create one SQL Backup log file.
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.