April 21, 2011 at 4:58 am
In our environment we do a full backup of our databases every evening and during the day we backup the transactional logs every 15 minutes. This has been working fine but recently the databases go into 'Restoring' whenever the the transaction log is backed up. The only way we can stop the databases going into 'restoring' is NOT to do any transaction log backup's.
I am at a lose as to why this is happening..
Rajesh Desai
April 21, 2011 at 5:22 am
how do you backup? what kind of tool?
April 21, 2011 at 5:24 am
The full databas backup's and transactionl log backup's are done using SQL Server Management Studio.
April 21, 2011 at 5:27 am
I would check your backup job. Unless your server has gone completely around the bend in a way I've never heard of, this command
BACKUP LOG MyDatabase TO DISK = 'somepath'
Will absolutely not cause this command to be run:
RESTORE DATABASE MyDatabase FROM blah,blah,blah
You must have something else running in there, someone modified your log backup job, something.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2011 at 5:27 am
Can you post the script you are using to do the log backup?
April 21, 2011 at 5:32 am
oke default maintenance plans it is i suppose then.
some entries in you SQL log after the "log was backupped" entry when the last log backup was made and the db's went into restoring?
check also the default trace, and then just near the audit Backup/Restore Event
April 21, 2011 at 5:37 am
I tried :
a) restoring the logs
b) changing the backup to simple and then back to full , taking a full backup
After a transaction lob backup ( which does not report any errors) the database goes into 'Restoring'. The only way I can get the database out of this mode is to run the following command RESTORE DATABASE xxxxx WITH RECOVERY
April 21, 2011 at 5:40 am
The only way that can happen is with a restore command.
Unless like grant said you just found the mother of all bugs in SQL Server.
April 21, 2011 at 5:44 am
Rajesh Desai-289863 (4/21/2011)
I tried :a) restoring the logs
b) changing the backup to simple and then back to full , taking a full backup
After a transaction lob backup ( which does not report any errors) the database goes into 'Restoring'. The only way I can get the database out of this mode is to run the following command RESTORE DATABASE xxxxx WITH RECOVERY
Let's be clear, step A) up there says "restoring the logs" Are you trying to BACKUP the logs or are you trying to RESTORE the logs. They are two, very different operations.
Move away from your automated jobs for a minute. Can you run this:
BACKUP DATABASE x TO DISK = 'somepath'
Do it on a database that has full recovery, for this test, a smaller database would work better. If that worked, can you run this:
BACKUP LOG x TO DISK = 'someotherpath'
If that completes, does that database then go into Restoring mode? If so, doing it the way I've described, you need to get in touch with Microsoft right away.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2011 at 5:50 am
What's the exact command you are using for the log backups?
I'm going to guess that you are doing this:
BACKUP LOG ... WITH NORECOVERY
That will back the log up, then take the database into a restoring state. It is not a normal log backup command, it's a tail-log, used only for when you are just about to restore the entire database from a backup.
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
April 21, 2011 at 8:38 am
I tried
BACKUP DATABASE x TO DISK = 'somepath' and then BACKUP LOG x TO DISK = 'someotherpath'
and both were successfull so I went to the database management options and found that the
'Back up the tail of the log, and leave the database in the restoring state' was SET ??
Unsetting this fix's the problem.
Thank you all for your help.
April 21, 2011 at 10:33 am
I think I guess you are taking log backup with tail option,if this option check then database is come to restoring state,check this option also
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 21, 2011 at 10:36 am
Syed Jahanzaib Bin hassan (4/21/2011)
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
Could you actually read the thread instead of just posting to rack up your points and traffic back to your site?? This issue has been resolved for hours and a couple of your previous posts too!
April 21, 2011 at 10:59 am
Ninja's_RGR'us (4/21/2011)
Could you actually read the thread instead of just posting to rack up your points and traffic back to your site??
You mean like this? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159568
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
April 21, 2011 at 11:04 am
GilaMonster (4/21/2011)
Ninja's_RGR'us (4/21/2011)
Could you actually read the thread instead of just posting to rack up your points and traffic back to your site??You mean like this? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159568
Wow it's almost more useless there. At least it's not wrong (unless I missed previous posts).
Thanks for the assist Gail, offcially getting Steve involved on this one.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply