April 1, 2010 at 11:00 pm
I have SQL 2005 db, I take log backup on every 5 minutes automatically. I have one confusion in that, suppose my last full backup was taken at 12:00, first log backup at 12:05 , second log backup at 12:10, I have made so many entries in table after second log backup that was taken at 12:10, my next log backup will be taken at 12:15, but at 12:14 my server gets rebooted. Now my question is, would I be able to restore all the records that were updated after 12:10 ? My database is showing in "Recovering" mode it has become inaccessible.
April 2, 2010 at 1:17 am
The restore procerss is fully documented in BOL
You to take a tail log backup so you capture all entries currently in the transaction log that have not been backed up.
You then need to restore the files, starting with the full backup taken at 1200 followed by 1205 log then 1210 log then the tail.
It would be wise to find out why the database has not recovered properly.
Gethyn Elliswww.gethynellis.com
April 2, 2010 at 2:33 am
Recovering or restoring?
I ask, because on this thread you said recovering and on another you said restoring. Which is it?
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 2, 2010 at 4:32 am
It is restoring.
April 2, 2010 at 4:44 am
Ok, very different from Recovering...
Means that there's either a database restore in process, a restore has been done and the norecovery option specified, or someone has done a log backup and specified the norecovery option (which is typically used when backing up the tail of the log before a restore)
Check if there's a database restore in process (query sys.dm_exec_requests). If not, try and bring the DB online with this
RESTORE DATABASE <db name> WITH RECOVERY
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 2, 2010 at 4:47 am
I tried this but the situation is still remaining same. I restored db with recovery option.
April 2, 2010 at 5:10 am
So there's no DB restore happening?
What happened when you ran the RESTORE with recovery command? Was there an error? Were there any results from the query?
If you check the SQL error log, are there any messages relating to this DB?
If you query sys.databases, what's the exact value of state_desc for this database?
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 2, 2010 at 5:18 am
When I restored the db with recovery option it restored successfully. But it is still showing restoring mode. I checked SQL error logs but no error is logged there pertaining to the db.
April 2, 2010 at 5:45 am
Did you refresh Object Explorer?
If you try and query that database, do you get an error? If so, exactly what error?
If you query sys.databases, what's the state_desc for this DB?
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 2, 2010 at 6:01 am
I am unable to query on that database.
April 2, 2010 at 6:28 am
If you try and query that database, do you get an error? If so, exactly what error?
If you query sys.databases, what's the state_desc for this DB?
Please remember I can't see what you're doing, I can't see what results your getting. The more details that you can give me, the greater the chance that I'll be able to help you.
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 2, 2010 at 6:43 am
state_desc for this DB is RESTORING and if I query a table (details) on this db i get following error:-
Msg 208, Level 16, State 1, Line 1
Invalid object name 'details'.
April 2, 2010 at 7:25 am
That error doesn't make the slightest sense. That the error you get when you're querying for an object in the wrong database, not querying an inaccessible database.
SELECT * FROM DatabaseName.sys.objects (replace DatabaseName with the name of the DB that is restoring)
Post exact message
Try
RESTORE DATABASE DatabaseName WITH RECOVERY (replace DatabaseName with the name of the DB that is restoring)
Post the EXACT message that you get running that.
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply