backup while running trn log backup
AlexG
Posts: 24
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
--script
declare
@db_name sysname ,
@sql varchar(8000)
declare C1 cursor for
select name from sysdatabases
where
dbid not in(2,3) --all exepet temp and model
open c1
fetch c1 into @db_name
@fetch_status = 0
begin
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
end
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 ?
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
--script
declare
@db_name sysname ,
@sql varchar(8000)
declare C1 cursor for
select name from sysdatabases
where
dbid not in(2,3) --all exepet temp and model
open c1
fetch c1 into @db_name
@fetch_status = 0
begin
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
end
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 ?
Comments
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.
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com