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.
| 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 backupsTo restore you start with FULL backupThen 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) |
 |
|
|
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 ShawSQL Server MVP |
|
|
|
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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-14 : 11:47:34
|
| "how do you backup the transaction log?"There are two separate backup commandsBACKUP DATABASE ...andBACKUP LOG ...see documentation for full details |
|
|
|
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 abcto disk='e:\backup\abc.trn' |
|
|
|
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 |
|
|
|
|
|
|
|
|
transaction log backup steps