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 |
|
kpgraci
Yak Posting Veteran
68 Posts |
 Posted - 2011-07-06 : 16:04:40
|
| I think I understand what a tail backup is, backing up the last set of transactions of a database. The purpose is to "prevent work loss and keeps the log chain intact". I understand this, but this next statement concerns me: "SQL Server 2005 and later versions usually require that you take a tail-log backup before you start to restore a database"require? As in MUST?It makes no sense to me, if I'm restoring from a point of failure, then the database has failed, so I want to get it back to prior to the failure, so why would I want to back up the tail of the log, I think I would want to discard that and restore to the point of failure. What I'm having trouble with is that if I'm restoring then there must have been a problem, I would not restore over a perfectly good database...right?So what's all the fuss about backing up the tail of the log unless I was just 'moving' the database rather than 'restoring' it?kpg |
|
|
kpgraci
Yak Posting Veteran
68 Posts |
Posted - 2011-07-06 : 16:16:17
|
OK...let's see if I'm leaning anything by trying to answer my own question.  The tlog backups + the tail log backup has the (what I call) commands issued to the database, so even thought the database has failed, the tlogs can restore it completly with no loss whatsoever, the only consquence to users being down time.close?kpg |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-06 : 16:32:37
|
| Yup, providing the log file is still available and SQL is still running.And yes, SQL 2005 will require you to backup the tail of the log if you are restoring OVER a working, online database, unless you use the REPLACE option. REPLACE tells SQL you don't care what you're overwriting. It's not if you're just restoring the DB.It's a safety thing. Maybe the DB is online but you need to restore because of corruption, but you don't want to lose any data at all. Last tran log backup was 20 min ago. If you start the restore at this point, you've lost 20 min of data. So SQL throws an error requiring you to take a tail log backup (backup log with norecovery in this case) or tom explicitly say that you don't are (restore with replace)--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Tail log backup confusion