| Author |
Topic |
|
inbs
Aged Yak Warrior
860 Posts |
 Posted - 2009-06-28 : 16:50:48
|
| i read the msdn and still dont undrstand ,give me example in life when i need use,a.I choose "Back up the tail of the log, and leave the database in the restoring state " instead Truncate transaction log?i understand that if i have disaster so i need to choose tail log backup why i cant use Truncate transaction log,In RESTORE, what i need to choose,what the steps of restore?b. when i need to do RESTORE WITH NORECOVERY?Best regards |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-06-29 : 07:57:10
|
| a) You would use that option when you want to backup the tail of the log and leave the DB in a state where no more changes can be made. Typically just before restoring. You would also use that if the mdf is damaged and you can't do a normal log backup.b) When you want to restore more backups, eg full, diff and several logs. If you restore with recovery, no more backups can be restored.--Gail ShawSQL Server MVP |
 |
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2009-06-29 : 09:35:05
|
| a. do you mean,if suppose i make TRUNCATE to table, and normal log backup do not work ,so i use tail log backup?b. do you mean if i restore full backup and some log backup, after that i cant resotre more backup? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-06-29 : 10:19:11
|
| a) Table truncation and log truncation have absolutely nothing to do with each other. May I suggest you read up on both in SQL books Onlineb) If you restore a backup WITH RECOVERY, you cannot restore more backups afterwards. Have a look in SQL books online for restore database.--Gail ShawSQL Server MVP |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2009-06-30 : 07:52:58
|
| first i read BOL more than one.i think you dont understand me in Question a.a.i have example of life,suppose6:00 full back up7:00 log back up8:00 log back up8:30 the DB is damaged (delete table ect.)1.what i have to do?2.i need to restore my logs+full backup from 6:00-8:00,am i right?3.where in this example i need tail log backup,if i dont need so give me example where i must to use it?you help a lot -thank you. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-06-30 : 09:03:16
|
| The tail log backup is the last backup of the log taken before you restore a database.In this example, you can just restore full plus logs from 6-8. If you do that, you lose all the transactions between 8 and 8:30. If you don't want to lose those, you would need to first take a tail log backup, then restore the full, then the 6am, 7am and 8am log backups, lastly restore the tail log backup that you just took (using Stop At if you want to stop before a delete table statement)--Gail ShawSQL Server MVP |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2009-06-30 : 13:19:49
|
| i start to understand you.you write "If you don't want to lose those, you would need to first take a tail log backup,"1-why i can't make a normal log backup in 8:35 for example?2-give me example of damage in my DB ,so i must do tail log back up and i can't do a noraml log backup.(-i RESTORE with RECOVERY in my example (6:00-8:30 backup with tail log?) or i need NORECOVERY)EXAMPLE for 1(from the MOC):1.i create a DB of Name AdventureWorkTest2.Create two tables,Customers,State3.Full back up to the DB4.UPDATE Customers (change the city)5.change the file name of the DB from AdventureWorkTest.mdf to AdventureWorkTest_broken.mdf (my DB is damaged)5.now my DB is inaccessible6.stand on one of my other DB and make normal Log BACK UP7.stand on one of my other DB and make RESTORE (i chose full + log)8.now i see the change in customers (because i restore my log -in step 7)why i can see the change in Customers table, i suppose that i need use tail log,NO?? |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-07-02 : 02:45:17
|
quote:
Originally posted by inbs
i start to understand you.you write "If you don't want to lose those, you would need to first take a tail log backup,"1-why i can't make a normal log backup in 8:35 for example?2-give me example of damage in my DB ,so i must do tail log back up and i can't do a noraml log backup.(-i RESTORE with RECOVERY in my example (6:00-8:30 backup with tail log?) or i need NORECOVERY)EXAMPLE for 1(from the MOC):1.i create a DB of Name AdventureWorkTest2.Create two tables,Customers,State3.Full back up to the DB4.UPDATE Customers (change the city)5.change the file name of the DB from AdventureWorkTest.mdf to AdventureWorkTest_broken.mdf (my DB is damaged)5.now my DB is inaccessible6.stand on one of my other DB and make normal Log BACK UP7.stand on one of my other DB and make RESTORE (i chose full + log)8.now i see the change in customers (because i restore my log -in step 7)why i can see the change in Customers table, i suppose that i need use tail log,NO??
did u really backup and restored the database after changing the mdf file name; how ???? |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2009-07-02 : 03:34:43
|
| sorry , i mean ndf. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-07-02 : 03:53:26
|
quote:
Originally posted by ahmad.osama
did u really backup and restored the database after changing the mdf file name; how ????
It's possible to back the log up if the data files are damaged or missing. It's not possible to back the full database up.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-07-02 : 04:01:08
|
quote:
Originally posted by inbs
i start to understand you.you write "If you don't want to lose those, you would need to first take a tail log backup,"1-why i can't make a normal log backup in 8:35 for example?
All that's meant by a tail log backup is the last log backup taken before restoring the database. Hence if you back the log up at 8:35 before starting the restore, that backup is known as a 'tail log backup', regardless of which options were specified.Usually WITH NORECOVERY is specified on a tail log backup to ensure that no more transactions can occur after the backup has completed.quote:
why i can see the change in Customers table, i suppose that i need use tail log,NO??
Probably because you restored the entire log backup, hence restoring the change made to Customers. If you want to restore a log but stop before the end of the log, you have to use STOPAT in the restore.See http://msdn.microsoft.com/en-us/library/ms179314.aspx and http://msdn.microsoft.com/en-us/library/ms179451.aspx--Gail ShawSQL Server MVP |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-07-02 : 07:55:32
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by ahmad.osama
did u really backup and restored the database after changing the mdf file name; how ????
It's possible to back the log up if the data files are damaged or missing. It's not possible to back the full database up.--Gail ShawSQL Server MVP
how ?? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-07-02 : 10:18:16
|
| BACKUP LOG .. TO DISK = ... WITH NO_TRUNCATE. It's backing the log up, just the log. It doesn't need to touch the data file. Providing the database is still attached, the log file is intact and the DB is in full recovery, the tail of the log can be backed up.See the first link that I posted above.--Gail ShawSQL Server MVP |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2009-07-05 : 02:16:08
|
quote:
All that's meant by a tail log backup is the last log backup taken before restoring the database. Hence if you back the log up at 8:35 before starting the restore, that backup is known as a 'tail log backup', regardless of which options were specified.
in managment studio ,when i back up the transaction log , i have two options:options->Transaction Log->1.Truncate the transaction log.2.Back Up the tail log,and leave the database in the restoring state.so if i back the log up at 8:35 , and choose first option, so it still be called "Tail Log"?? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-07-05 : 08:00:53
|
According to BoL, yes. As per that like I gave you:quote:
In most cases, under the full or bulk-logged recovery models, SQL Server 2005 and later versions require that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.
In most cases, a tail log backup will have either the NORECOVERY option (so that no more transactions can be made against that DB) or the NO_TRUNCATE option (when the DB is offline or otherwise damaged)If you haven't read those links, please do so.--Gail ShawSQL Server MVP |
|
|
|
|
tail log and restore with NORECOVERY