Transaction Logging Backup Set

[Thiko]

Hi

I take one nightly full database backup at 02:00 and backup the
transaction log to one backup set every 15mins.

The commands to do this are as follows and are set up to run as
database jobs:

-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT

-- Transaction Log Backup Every 15mins.

BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
RETAINDAYS = 0

At the moment the transaction log backups every 15mins are simply
being added one by one to the one backup set. I want the backed up
transaction logs in the backup set after 24hrs to be overwritten so
disk space does not add up. I thought RETAINDAYS would do this as an
expirydate is set to expire on the time it is created so i would have
expexted it to be overwritten when that time comes around the next
day. (But is doesent)

I think it is ok to overwrite the transaction log in the backupset
after 24hrs as you only need the transaction logs since the last full
backup (which happens nightly at 02:00) for recovery.

Does anyone have any ideas?

Many thanks.

Thiko!

[Igor Raytsin]

-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT

-- After that (once a day)
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH INIT ...

-- Transaction Log Backup Every 15mins.
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT

"Thiko" <bi*****@hotmail.com> wrote in message
news:b8**************************@posting.google.c om...

Hi

I take one nightly full database backup at 02:00 and backup the
transaction log to one backup set every 15mins.

The commands to do this are as follows and are set up to run as
database jobs:

-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT

-- Transaction Log Backup Every 15mins.

BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
RETAINDAYS = 0

At the moment the transaction log backups every 15mins are simply
being added one by one to the one backup set. I want the backed up
transaction logs in the backup set after 24hrs to be overwritten so
disk space does not add up. I thought RETAINDAYS would do this as an
expirydate is set to expire on the time it is created so i would have
expexted it to be overwritten when that time comes around the next
day. (But is doesent)

I think it is ok to overwrite the transaction log in the backupset
after 24hrs as you only need the transaction logs since the last full
backup (which happens nightly at 02:00) for recovery.

Does anyone have any ideas?

Many thanks.

Thiko!

[Thiko]

Hi!

This is very helpful. Thank you very much.

Could you tell me please why to truncate the transaction log
immediately after the full database backup command? What happens if I
need to recovery some of the transactions that were taking place
during the full database backup, for example? Would it be better to
do the truncate transaction log with the INIT immediately before the
full database backup takes place?

Many Thanks.

Thiko!

"Igor Raytsin" <n&*@cyberus.ca> wrote in message news:<3f********@news.cybersurf.net>...

-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT

-- After that (once a day)
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH INIT ...

-- Transaction Log Backup Every 15mins.
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT

"Thiko" <bi*****@hotmail.com> wrote in message
news:b8**************************@posting.google.c om...

Hi

I take one nightly full database backup at 02:00 and backup the
transaction log to one backup set every 15mins.

The commands to do this are as follows and are set up to run as
database jobs:

-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT

-- Transaction Log Backup Every 15mins.

BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
RETAINDAYS = 0

At the moment the transaction log backups every 15mins are simply
being added one by one to the one backup set. I want the backed up
transaction logs in the backup set after 24hrs to be overwritten so
disk space does not add up. I thought RETAINDAYS would do this as an
expirydate is set to expire on the time it is created so i would have
expexted it to be overwritten when that time comes around the next
day. (But is doesent)

I think it is ok to overwrite the transaction log in the backupset
after 24hrs as you only need the transaction logs since the last full
backup (which happens nightly at 02:00) for recovery.

Does anyone have any ideas?

Many thanks.

Thiko!

[Igor Raytsin]

Hi,

There is some misunderstanding here:

1. You do not need to truncate transaction log manually. Full Backup or
Transaction Log Backup will do it for you.
2. INIT option does not truncate log. INIT specifies that all backup sets
should be overwritten.
Regards,
Igor
"Thiko" <bi*****@hotmail.com> wrote in message
news:b8**************************@posting.google.c om...

Hi!

This is very helpful. Thank you very much.

Could you tell me please why to truncate the transaction log
immediately after the full database backup command? What happens if I
need to recovery some of the transactions that were taking place
during the full database backup, for example? Would it be better to
do the truncate transaction log with the INIT immediately before the
full database backup takes place?

Many Thanks.

Thiko!

"Igor Raytsin" <n&*@cyberus.ca> wrote in message

news:<3f********@news.cybersurf.net>...

-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT

-- After that (once a day)
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH INIT ...

-- Transaction Log Backup Every 15mins.
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT

"Thiko" <bi*****@hotmail.com> wrote in message
news:b8**************************@posting.google.c om...

Hi

I take one nightly full database backup at 02:00 and backup the
transaction log to one backup set every 15mins.

The commands to do this are as follows and are set up to run as
database jobs:

-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT

-- Transaction Log Backup Every 15mins.

BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
RETAINDAYS = 0

At the moment the transaction log backups every 15mins are simply
being added one by one to the one backup set. I want the backed up
transaction logs in the backup set after 24hrs to be overwritten so
disk space does not add up. I thought RETAINDAYS would do this as an
expirydate is set to expire on the time it is created so i would have
expexted it to be overwritten when that time comes around the next
day. (But is doesent)

I think it is ok to overwrite the transaction log in the backupset
after 24hrs as you only need the transaction logs since the last full
backup (which happens nightly at 02:00) for recovery.

Does anyone have any ideas?

Many thanks.

Thiko!