February 15, 2011 at 10:40 am
hi guys,
i am new to SQL DBA.Today one prod server database is not responding.i got information from error log database is inaccessible.i have taken yesterday 10 p.m backup and log backup is every 1 hour backup.atabase crash time is 10:40 A.M. i want recover database with out any data loss.
could you please give me the suggestions.
Regards
venkat
Regards
venkat
February 15, 2011 at 10:45 am
What recovery model was your database set at? FULL, BULK LOGGED, or SIMPLE?
EDIT: Also, do you see the database in SSMS? Is it perhaps in Suspect mode instead of just gone?
February 15, 2011 at 12:37 pm
database recovery model is full.
you have any idea .
Regards
venkat
February 15, 2011 at 12:41 pm
If db recovery is full, then your last full backup and every transaction log since can be used for restoring the database. Go into SSMS -> Databases, right click and choose "Restore Database". The GUI is fairly self explanatory. Picking the time to restore to should be on the second tab.
BUT, the reason I ask about the database is that if it's Suspect, there may be other options for you that don't require a restore. Restores should always be a last resort.
So, look in SSMS and see if you can see the database. If you hover your mouse about it, what does it say?
February 15, 2011 at 1:19 pm
Brandie Tarvin (2/15/2011)
BUT, the reason I ask about the database is that if it's Suspect, there may be other options for you that don't require a restore. Restores should always be a last resort.
Not at all. Repairs are a last resort, because of the potential data loss. If the DB is suspect (not recovery pending) then it means that something is damaged and that the DB is probably inconsistent. In most cases, suspect requires repair or restore, and it'll be repair allow data loss.
With full and log backups, restore will be able to get the DB up with minimal or no data loss (depending whether a tail log backup can be taken)
Before deciding on a method of recovery, we need the database state (state_desc from sys.databases) and all errors relating to the DB from the SQL error log.
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
February 15, 2011 at 1:21 pm
hi,
thank you for the response.
i am having the yesterday backup 10p.m and every 1 hour log backups.
we can use the backups and restore the database up to 10 A.M .i do not have the 40 minutes data . is it possible to recovery the data?
Regards
venkat
February 15, 2011 at 1:45 pm
Did the 11 am log backup attempt to run ?
February 15, 2011 at 1:46 pm
GilaMonster (2/15/2011)
Before deciding on a method of recovery, we need the database state (state_desc from sys.databases) and all errors relating to the DB from the SQL error log.
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
February 15, 2011 at 1:50 pm
Since you have the transaction log backups scheduled to run on the hour, the forty minutes after the last log file is lost.
If management determines that 59 minutes of lost data is too much, then it is important that you change the frequency of the log file backup.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
February 15, 2011 at 3:29 pm
Meet George Jetson (2/15/2011)
Since you have the transaction log backups scheduled to run on the hour, the forty minutes after the last log file is lost.
Not necessarily. Depending on what's damaged and how badly there could be anything from 0 to 40 minutes of data loss. That's why it's important to know what's actually wrong before attempting to suggest fixes.
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
February 16, 2011 at 3:15 am
11 a.m backup job failed.
can i run dbcc checkdb(databasename ,repair_allow_data_loss)
Regards
venkat
February 16, 2011 at 3:18 am
That's like asking a car mechanic if he can replace the engine without him knowing the slightest thing about what is wrong with the car.
Once more with feeling...
GilaMonster (2/15/2011)
GilaMonster (2/15/2011)
Before deciding on a method of recovery, we need the database state (state_desc from sys.databases) and all errors relating to the DB from the SQL error log.
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
February 16, 2011 at 4:00 am
if we select database it's showing error. database is not operational
Regards
venkat
February 16, 2011 at 4:10 am
if we select database it's showing error. database is not operational
what Gail is referring is to run this query in master database:-
select name, state,state_desc from sys.databases
and report about your database stats.
----------
Ashish
February 16, 2011 at 4:12 am
thank you for all the suggestions
Regards
venkat
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply