Does this make sense to do this? (Backup Log with Init)

[bessebo]

Let's assume that I perform a full backup on Saturday night and it is successful. On Sunday morning I perform a reindexing of our database and as a result this reindexing activity causes a lot of changes to our database. I know that this reindexing is done by 6:30AM and sometimes we have people come in at 8:00 on Sunday to do some work. So I was thinking of performing two of these in a row:

Backup Log 'database name' to 'log backup device' with Init.

The first one will perform a backup of all of the transactions resulting in a large transaction log backup file but it looks like the 2nd one actually makes the transaction log backup small and initializes it.

Then, after these are complete, I can now start running:

Backup Log 'database name' to 'log backup device' with NOINIT

since there is now activity that I want to keep (people actually performing transactions)...

I am just trying to think of the best way to remove those re-indexing transactions from by transaction log backup as soon as I can so I don't have to move this huge file off to tape. Any suggestions would be appreciated...

Regards,
Bessebo

 

[mrdenny]

If you wipe out an existing transaction log backup in a backup file you will not be able to roll forward the new transaction logs. If you wanted to do it the way that you are saying you need to put a full backup directly before the second transaction log backup.

If you were going to do it this way there would be no point in actually backing up the transaction log. Simply truncate the log and then do a full backup. However this can cause problems if you have to do a restore and your full backup is corrupt, lost, etc.

The proper procedure would be to keep the large transaction log backup just like every other transaction log backup as without it all your other log backups are useless.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bessebo]

Thanks Mr Denny. I thought a little more about it after writing that post and it does make sense to keep that transaction log backup. It is just that this large transaction log backup after the rebuilding of indexes, is causing some disk space issues. I was starting to think about another solution. How about purchasing an external disk drive (say a 250GB drive) and use this for backing up the post-reindexing transaction log. Just have a separate backup device that points to this external drive. Then I will have it should I ever need it. Then every week I can just perform a backup log with init to that backup device.

What do you think? By the way, thanks so much for all of your thoughtful responses to my posts.

Regards,
Bessebo

 

[bessebo]

Mr Denny,
I have modified my backup procedures on Sunday which is when I rebuild indexes. This is the order I do things in now:

Sunday Schedule

5:00AM - Rebuilding of indexes
5:00PM - transaction log backup with noinit (there are usually no transactions taking place on Sunday so no need to take more transaction log backups)
7:00PM - Full database backup
8:00PM - transaction log backup with init

I think this should result in a small transaction log backup after the transaction log backup with init. Now we are rady for our Monday morning business week processing. Do you concur?

By the way, right now we perform all of our backups as SQL backups to disk and then our network backups take those files off to tape each evening. I am thinking that it may make sense to purchase the Backup Exec SQL Agent so that the full backups and transaction log backups can occur through Backup Exec rather than how we are currently doing them, right within Enterprise Manager.

Regards,
Bessebo

 

[mrdenny]

That backup schedule looks good.

You can go with the backup to the externial disk, put a single disk does give you a single point of failure.

I prefer to not use the agents, and instead backup to disk, the have the backup software grab the backups. It does add a little time to backup and restore, but it is easier to manage.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bessebo]

Thanks very much. I really appreciate your sage advice. I think all will work out well tonight with the new procedures in place..

Regards,
Bessebo