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 |
|
tanu
Yak Posting Veteran
57 Posts |
 Posted - 2008-08-18 : 02:53:16
|
| I am really concerned about backup of database. While doing transacion log backup when we go to option page there are two option buttons for transaction log.1. Truncate the transaction log2. Back up the tail of the log and leave the database in the restoring state.What are these options? I have transaction log backup every hour and two differential backup and one full backup.If I need to restore database I will start with full backup and then apply first differential backup,second differential backup then remaining transaction log backups to it.I have these backups in three different folders on the server.Then we have increamental tape backup of everyday.What is the best practise? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-08-18 : 04:10:06
|
| Backup the tail is usually used just before restoring a DB for whatever reason. It leaves the database in a recovering state (unable to access)If you need to restore, you would restore the full backup, then the latest diff and then the transaction logs one by one. Diffs contain the changes since the last full backup.--Gail ShawSQL Server MVP |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-18 : 07:53:52
|
| Donot truncate log as it will break log chain. |
|
|
|
tanu
Yak Posting Veteran
57 Posts |
Posted - 2008-08-18 : 14:31:28
|
| But there are only two options available from this widow and I have to choose one if I want to create job from Management studio.I know both option are not right for what I am doing. any suggestion would be great. 1. Truncate the transaction log2. Back up the tail of the log and leave the database in the restoring state. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-18 : 23:18:30
|
| Why don't just backup log with sql statement? |
|
|
|
suresha_b
Yak Posting Veteran
82 Posts |
Posted - 2008-08-19 : 08:53:11
|
| >> any suggestion would be great.Don't backup manually.Create Maintenance Plan. It will create SQL Server Agent scheduled jobs. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-19 : 23:50:52
|
| You can backup log in sql job without maintenance plan. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-08-20 : 02:59:45
|
quote:
Originally posted by tanu
But there are only two options available from this widow and I have to choose one if I want to create job from Management studio.I know both option are not right for what I am doing. any suggestion would be great. 1. Truncate the transaction log2. Back up the tail of the log and leave the database in the restoring state.
The first one is a little misleading. They're options for what SQL does to the log after it's backed it up. The first is the default, what you would have happen if you issued a BACKUP LOG xyz TO DISK with no other options.SQL backs up the transaction log, then truncates the section that it has just backed up.The second option is for when your database is corrupt or you are restoring over the top of the database. SQL backs up the log, does not truncate the section it has backed up and it sets the database into a recovering state after the backup completes--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Transaction log backup