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 2005 Forums
 SQL Server Administration (2005)
 transaction log backup steps

Author  Topic 

sleonard24
Starting Member

5 Posts

Posted - 2010-12-10 : 12:58:40
I am new to sql server administration and am looking for some information on how to backup the transaction log for an SQL 2005 database.

The database I have is set to full recovery mode. I just started backing up the database and noticed the transaction log was getting rather large in size. After much reading this morning in this forum and on the Internet, I realized that I also need to backup my transaction log to prevent the log from overtaking all the hard disk space.

I currently have a maintenance plan where I have backed up the database with a backup type of full. How do I then backup the transaction logs afterward since I know that the backup has to be done first? Also, after I backup the database and the log, is/are there any other steps I need to complete to make the transaction log smaller? Or, do I leave it at is current size? I read some posts about truncating the log, but am now sure if I need to do this.

I plan on doing full backups of the database daily once I have everything set to go.

Thanks for any advice/tips/steps that are replied. I really appreciate them.

Scott

Kristen
Test

22859 Posts

Posted - 2010-12-11 : 03:17:33
Backup the Transaction Log every 15 minutes (more often has little gain, less often results in bigger files and potentially an extended period of data loss in a Disaster). Once you have done the first one you may need to SHRINK the Log File (LDF) back down to a reasonable size (but leave it large enough that it doesn't have to grow back again - repeated Shrink/Grow will fragment it.

That's it!

Log backups are completely independent of Full / Differential backups

To restore you start with FULL backup
Then optionally a later differential backup (must not have been any other FULL backups in between)
And then optionally one, or more, Log backups taken after the Full/Differential backup (must start with the one after the Full/Differential backup, and you cannot miss any out)

So ... lets say your Full backup is corrupted / lost, you can go back to an earlier FULL backup and just apply ALL the Log backups since.

You can apply the final LOG backup using "STOPAT" to restore to a specific time (i.e. part way through the backup - e.g. "I accidentally deleted all the Customers at 11:27" ))

If your database becomes corrupted you can (usually) make a final "tail" Log backup, then restore from FULL + all subsequent LOG backups, including the "Tail" backup, and have zero data loss.

Check the sizes of the backup files over the course of a week. If there is a particular time where you get "monster" sized Log Backups then review what is running at that time. The normal culprit is Index Rebuild (we explicitly run Log backups every 2 minutes during index rebuilds to stop the Log file growing unacceptably large)
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-11 : 07:55:56
Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP
Go to Top of Page

sleonard24
Starting Member

5 Posts

Posted - 2010-12-14 : 10:17:12
Kristen:

Pardon the newbie question here. But, how do you backup the transaction log? I am not sure of the steps to do so.

The rest of the information is very good and makes sense.

Thank you for taking the time to reply.

Scott
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-12-14 : 11:47:34
"how do you backup the transaction log?"

There are two separate backup commands

BACKUP DATABASE ...
and
BACKUP LOG ...

see documentation for full details
Go to Top of Page

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2010-12-14 : 12:51:33
backup log <database_name>
to disk='path\filename.trn'

i.e.

backup log abc
to disk='e:\backup\abc.trn'

Go to Top of Page

AlexGreen
Starting Member

8 Posts

Posted - 2011-07-24 : 12:06:13
First of all, you don't need to backup the Transaction Log to avoid unlimited growth. In contrast, backing up the TL does not implies the file will be shrunken. The simpler approach is to set a size limit to the file. From SQL Management Studio right-click on your database, select properties, then choose the Files option on the left and set the Autogrowth limit.

According to you, all you need is a full backup once a day, then I recommend you to try SQL Backup and FTP, it is much easier to use than SQL built-in tools, you can backup more than one database at at time, and if you really need further options, it also lets you run and schedule full, differential and transaction log backups.
unspammed
Go to Top of Page
   

- Advertisement -