backing up transaction log - SQL Server Forums

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)

backing up transaction log

Author

Topic

federicot
Starting Member

6 Posts

Posted - 2004-04-21 : 14:36:10

i have a question about tlog backup. in my scenario i perform a backup of my tlogs every 10 minutes. my question is..what kind of backup im performing? its a full or diferencial? coz in the wizard i didnt see any option to configure the type of the backup.
if the backup is diferential...i should have 1 file every 10 minutes?

thanks a advance..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-21 : 14:41:32

Transaction log backups are not full or differential. They are transaction log backups. Here are the various commands:

--Full backup
BACKUP DATABASE DBName
TO DISK = 'C:\DBName.BAK'

--Differential backup
BACKUP DATABASE DBName
TO DISK = 'C:\DBName.BAK'
WITH DIFFERENTIAL

--Transaction log backup
BACKUP LOG DBName
TO DISK = 'C:\DBName.TRN'

So as you can see, transaction log is different.

And yes you will have one file every 10 minutes according to what you have described.

I would some reading in SQL Server Books Online in the topic "Transaction Log Backups".

Tara

federicot
Starting Member

6 Posts

Posted - 2004-04-21 : 14:47:54

thanks for you assistance tara

quote:
Originally posted by tduggan

Transaction log backups are not full or differential. They are transaction log backups. Here are the various commands:

--Full backup
BACKUP DATABASE DBName
TO DISK = 'C:\DBName.BAK'

--Differential backup
BACKUP DATABASE DBName
TO DISK = 'C:\DBName.BAK'
WITH DIFFERENTIAL

--Transaction log backup
BACKUP LOG DBName
TO DISK = 'C:\DBName.TRN'

So as you can see, transaction log is different.

And yes you will have one file every 10 minutes according to what you have described.

I would some reading in SQL Server Books Online in the topic "Transaction Log Backups".

Tara

federicot
Starting Member

6 Posts

Posted - 2004-04-21 : 14:55:44

one more question about tlogs. now ive a 1gb tlog been backuped every 10 minutes. i know that after the backup is completed the tlog is truncated. ive configured my tlog with no restrictions in the autogrow option.
my question is...if the tlog is truncated i supose i dont have to worry about the size of the tlog and the free space in my disk coz after the backup the tlog is truncated right?.

tell if i dont being clear enought.

quote:
Originally posted by federicot

thanks for you assistance tara

quote:
Originally posted by tduggan

Transaction log backups are not full or differential. They are transaction log backups. Here are the various commands:

--Full backup
BACKUP DATABASE DBName
TO DISK = 'C:\DBName.BAK'

--Differential backup
BACKUP DATABASE DBName
TO DISK = 'C:\DBName.BAK'
WITH DIFFERENTIAL

--Transaction log backup
BACKUP LOG DBName
TO DISK = 'C:\DBName.TRN'

So as you can see, transaction log is different.

And yes you will have one file every 10 minutes according to what you have described.

I would some reading in SQL Server Books Online in the topic "Transaction Log Backups".

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-21 : 14:58:59

Yes that is true. It might still grow though if a large transaction runs. So you can't assume that it won't ever get over 1GB. Such a transaction would be DBCC DBREINDEX, which is also know as the optimizations in the database maintenance plan wizard.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources