July 29, 2010 at 7:17 am
Tom.Thomson (7/29/2010)
Amazing, especially the one from someone who's apparently an MVP.
I'm sure we've all said dumb stuff at times, but reading that thread there seemed to be a strange reluctance to admit that backing up the log under the SIMPLE recovery model is darn near impossible. It's surely not that hard to admit a genuine mistake? I don't think the MVP award is automatically revoked if you're ever wrong about something! 😀
BTW I say 'darn near impossible' because I have never tried to back up the tail of the log for a damaged simple-mode database. I would expect it to fail, but I have no direct experience.
I'm not sure what use a tail-log backup would be, other than for use with magic log-reader software.
Anyway, I'll see your Amazed and raise you an Agog
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 29, 2010 at 7:57 am
Paul White NZ (7/29/2010)
BTW I say 'darn near impossible' because I have never tried to back up the tail of the log for a damaged simple-mode database. I would expect it to fail, but I have no direct experience.I'm not sure what use a tail-log backup would be, other than for use with magic log-reader software.
I have no experience in this specific scenario either, however, BOL actually says that tail-log backups are for Bulk-Logged and FULL recovery databases. So your first assumption is correct. You can't tail-log backup a SIMPLE recovery database. But it won't fail. Why? Because the option isn't available to you, therefore, if you can't start one, it can't fail. @=)
Backing up the tail of the log is different from an actual log backup. You back up the tail to grab the data that has not yet been committed to the database. However, if a log file is damaged, you can't backup the tail. Or might not be able to, anyway. Regardless, SQL requires the tail-log backup done before a restore to capture that information and to re-apply it (if I understand this correctly) to the log file so that this data can be committed to the database properly if you restore the database up to that point.
Books Online 2005
SQL Server 2005 usually requires that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact. When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.Not all restore scenarios require a tail-log backup. You do not have to have a tail-log backup if the recovery point is contained in an earlier log backup, or if you are moving or replacing (overwriting) the database. Also, if the log files are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost. For more information, see "Restoring Without Using a Tail-Log Backup" later in this topic.
July 29, 2010 at 8:05 am
Brandie,
LOL! Yes I know how tail-log backups work and what they're for 😛
I've just never actually tried it with a *damaged* simple-recovery database, which is the only case I am not sure about. All other times you get an error, such as:
[font="Courier New"].Net SqlClient Data Provider: Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
.Net SqlClient Data Provider: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.[/font]
When I said I wasn't sure what possible use a tail-log backup would be, it was in the context of that thread, and with a damaged simple-recovery database in particular.
Thanks for the refresher though! Heh. Still giggling.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 29, 2010 at 8:13 am
DOH. Sorry. :pinch:
July 29, 2010 at 8:21 am
Brandie Tarvin (7/29/2010)
DOH. Sorry. :pinch:
No worries! It gave me a laugh, and it's all good information, so only good things happened 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 29, 2010 at 8:30 am
Hey Lynn, Good news regarding your daughter. You should brag about this... 🙂
-Roy
July 29, 2010 at 8:42 am
Paul White NZ (7/29/2010)
I've just never actually tried it with a *damaged* simple-recovery database, which is the only case I am not sure about.
Damaged is not going to change the situation. Simple recovery = no log backup. No point, the log backup, even if it could be taken, couldn't be restored, hence there's no reason for SQL to allow it.
Interesting enough, the error message is different to the normal one.
Results of test:
SELECT name, recovery_model_desc, state_desc FROM sys.databases WHERE name = 'testingsuspect'
Result: TestingSuspectSIMPLERECOVERY_PENDING
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
If you're interested in repo:
CREATE DATABASE [TestingSuspect]
GO
ALTER DATABASE [TestingSuspect] SET RECOVERY SIMPLE
GO
BACKUP DATABASE [TestingSuspect] TO DISK = 'D:\Develop\Databases\Backups\testingSuspect.bak' -- there is a current database backup
GO
ALTER DATABASE [TestingSuspect] SET OFFLINE
GO
-- open mdf in hex editor at this point and overwrite part of the first couple pages.
ALTER DATABASE [TestingSuspect] SET ONLINE
go
SELECT name, recovery_model_desc, state_desc FROM sys.databases WHERE name = 'testingsuspect'
BACKUP LOG testingsuspect TO DISK = 'D:\Develop\Databases\Backups\testingSuspect.trn' WITH NO_TRUNCATE -- tail log backup of damaged database
/*
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
*/
DROP DATABASE [TestingSuspect] -- will likely need to clean up files manually due to DB state at the time it was dropped
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2010 at 8:45 am
Paul White NZ (7/29/2010)
Tom.Thomson (7/29/2010)
Amazing, especially the one from someone who's apparently an MVP.I'm sure we've all said dumb stuff at times, but reading that thread there seemed to be a strange reluctance to admit that backing up the log under the SIMPLE recovery model is darn near impossible. It's surely not that hard to admit a genuine mistake? I don't think the MVP award is automatically revoked if you're ever wrong about something! 😀
He's acted that way just about each time I've disagreed with him.
The one time it turned out I was wrong. I'd glanced quickly at an exec plan and drew a conclusion that, upon a deeper investigation, turned out to be wrong. Got almost an 'I told you so!' feeling from him on reply.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2010 at 8:49 am
GilaMonster (7/29/2010)
Results of test:....
If you're interested in repo:
....
-- open mdf in hex editor at this point and overwrite part of the first couple pages.
Is this what you did to force it into Suspect mode? I've been trying to figure out a good way to do that for ages.
Thanks!
July 29, 2010 at 8:54 am
Brandie Tarvin (7/29/2010)
GilaMonster (7/29/2010)
Results of test:....
If you're interested in repo:
....
-- open mdf in hex editor at this point and overwrite part of the first couple pages.
Is this what you did to force it into Suspect mode? I've been trying to figure out a good way to do that for ages.
If you look, it didn't go suspect. It went recovery_pending. That's because the corruption was encountered while opening the file. To get it suspect, the corruption has to be encountered due to a rollforward/rollback.
I have a similar trick to force a DB suspect, but it's more complex.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2010 at 9:04 am
GilaMonster (7/29/2010)
If you look, it didn't go suspect. It went recovery_pending. That's because the corruption was encountered while opening the file. To get it suspect, the corruption has to be encountered due to a rollforward/rollback.I have a similar trick to force a DB suspect, but it's more complex.
I haven't actually run the code yet cause I'm in the middle of a project. But I intend to run it later. That's good information to have on the cause of SUSPECT. I confess not having read up on Suspect dbs much because I haven't been forced to do that particular fire drill. I'd better get my practice in before I need it, though. @=)
July 29, 2010 at 9:08 am
If you want to play...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2010 at 9:12 am
For those who are interested I've got a similar trick for Oracle.
It's very simple: just run restore database...
I've just restored a 300GB database to discover it's corrupted in many datafiles. I asked the Oracle consultant and he told me it's by design, beacase the tables were initially loaded with NOLOGGING option (it's something similar to minimal logging in bulk insert).
I am just wondering why we take backups...
-- Gianluca Sartori
July 29, 2010 at 9:13 am
Hey Lynn, congrats to Kassondra!! West Point sounds better than the places you've mentioned a few thousand posts back... A LOT better!!
Kassondra, you and your whole family can be very, very proud!!
July 29, 2010 at 9:41 am
Gianluca Sartori (7/29/2010)
... it's by design, beacase the tables were initially loaded with NOLOGGING option
And people still ask for a way to run SQL transactions without any logging....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16,876 through 16,890 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply