Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Scheduling Backup / Transaction Logs

Author  Topic 

SamC
White Water Yakist

3467 Posts
Posted - 2005-10-25 : 10:04:08
I've got Transaction logs scheduled for every 15 minutes. This means there is a Transaction Log backup scheduled for *almost* the same moment as the nightly backup.

Seems like something to avoid? Is there a way to skip a 15 minute transaction log backup easily?

Kristen
Test

22859 Posts
Posted - 2005-10-25 : 11:01:47
We have a SProc that does them. It has a table of what's due when. It does ALL the backups sequentially (i.e. each database in turn, TLog first followed by Diff or Full. It skips the DIFF if a FULL is "overdue", but it does do the TLog first if a Full or Diff is due.

Dunno if that helps though!

Kristen
Go to Top of Page

schuhtl
Posting Yak Master

102 Posts
Posted - 2005-10-25 : 11:07:10
You could do something like the following...
Step1 - sp_update_job @job_name = N'Tlog backup', @enabled= 0
step2 - backup database
step3 - sp_update_job @job_name = N'Tlog backup', @enabled= 1
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2005-10-25 : 11:26:33
I think what would be good would be a test for the time that could be executed in the step....

IF GETDATE() = Midnight EXEC Backup

I'll sort it out that way...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-10-25 : 14:32:53
Why do you want to avoid performing a full backup and tlog backup at the same time? They do different things.

Tara
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2005-10-25 : 14:50:25
quote:
Originally posted by tduggan

Why do you want to avoid performing a full backup and tlog backup at the same time? They do different things.

Ahhh... the Goddess speaks!

I was waiting to see if anyone would wonder about just that. I was wondering if I should be concerned about it creating too much disk banging or not.

Let's kick this discussion up a notch... BAM! What about a transaction log backup firing off during a REINDEX procedure? Should I be taking steps to ensure this doesn't happen?

Sam
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-10-25 : 14:55:23
You actually want a tlog backup to occur while the reindex is happening so that the tlog doesn't grow.

Tara
Go to Top of Page

schuhtl
Posting Yak Master

102 Posts
Posted - 2005-10-25 : 14:55:36
Full backups and transaction log backups do not occur at the same time. Trasaction log backups are paused until the full backup completes.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-10-25 : 15:13:39
Isn't it advisable to do a TLog backup just before a full backup - then if you need to restore Full + Tlogs there is less "stuff" for the restore to wade through in the first Tlog backup file to be restored - or is it a pedantic point only?

Kristen
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2005-10-25 : 15:19:45
quote:
Originally posted by tduggan

You actually want a tlog backup to occur while the reindex is happening so that the tlog doesn't grow.
quote:
Originally posted by schuhtl

Full backups and transaction log backups do not occur at the same time. Trasaction log backups are paused until the full backup completes.
quote:
Originally posted by Kristen

Isn't it advisable to do a TLog backup just before a full backup - then if you need to restore Full + Tlogs there is less "stuff" for the restore to wade through in the first Tlog backup file to be restored - or is it a pedantic point only?

RUMBLE !
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-10-25 : 15:20:04
I'm not sure that it matters. You just need to ensure you have the entire tlog chain to do a point in time recovery.

If you didn't do a tlog backup prior to the full, and you had some failure and couldn't use this full, then you'd use the last full plus all tlog backups including the one after the full that failed. You'd have everything.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-10-25 : 15:23:00
quote:
Originally posted by schuhtl

Full backups and transaction log backups do not occur at the same time. Trasaction log backups are paused until the full backup completes.


True, but the jobs could kick off at the same time. Just means that the tlog job is pending complete of the full. So it only delays the tlog job a bit.

Tara
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2005-10-25 : 15:48:36
quote:
Originally posted by tduggan

True, but the jobs could kick off at the same time. Just means that the tlog job is pending complete of the full. So it only delays the tlog job a bit.
Then special scheduling isn't needed, but is this application specifc?

How is this affected if a 3rd party product like litespeed or Red Gate is scheduled?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-10-25 : 15:58:12
You'd have to contact MS and/or the third party product to check on this as I don't know. You shouldn't have full backups and tlog backups scheduled from two different products anyway, so it's a moot point.

Tara
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2005-10-26 : 01:17:08
you can add a condition in your tlog backup that if the job is still running for your nightly backup, do not perform any tlog backup yet

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-10-26 : 01:26:56
"if the job is still running for your nightly backup"

Jen, have you got a code snippet lying around for that? Sounds handy ...

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-10-26 : 03:32:10
quote:
Originally posted by Kristen

"if the job is still running for your nightly backup"

Jen, have you got a code snippet lying around for that? Sounds handy ...

Kristen


To see if a job is running I use this:
exec master.dbo.xp_sqlagent_enum_jobs 1, ''
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2005-10-26 : 04:22:08
For you Kristen

CREATE procedure spDatabaseBackup
	@dbName varchar(50), --databaseName
	@backupName varchar(20), --shortened database name for backup name, i encounter a problem when the backup name is long
	@job_name varchar(4000), --job that you're checking, i have a naming convetion, which makes this sproc generic
	@path varchar(100)  -- the path minus the filename (this will be 'computed' by the code) where you'll save the backup file
as

set nocount on

declare @device nvarchar(500)
declare	@dest nvarchar(1000)
declare @sql nvarchar(4000)

set @device= @backupName + 'bkp' + convert(nvarchar(20),getdate(),112) 

set @dest=@path + @device +  '.bak'

if not exists (select * from master..sysprocesses where program_name=@job_name) -- this checks if the job is running
begin
	if exists(select * from master..sysdevices where [name]=@device)
		set @sql='BACKUP LOG  ' + @dbname + '
		TO ' + @device + '
		with description = ''Backup Log File for full Log''' + ',
		name = ''' + @dbname + ''''
	
	else
		begin
			EXEC sp_addumpdevice 'disk',@device,@dest
			set @sql = 'BACKUP database  ' + @dbname + '
			TO ' + @device + '
			with description = ''Full Database Backup''' + ',
			name = ''' + @dbname + ''''
		end

	if @sql is not null
		exec sp_executesql @sql

end

set nocount off


GO


--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-10-27 : 01:37:25
Thanks Rocky & Jen

Kristen
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2005-10-27 : 10:50:55
Jen can cook!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site