September 13, 2009 at 1:23 pm
Hi
my customers have a sql server 2000 database.
recovery model is simple.
my customer get full backup about 4 day ago.
today, accidently check eventViewer and found some pages has been corrupted.
i think i must restore page. but it's recovery model is simple.
my questions
1. How to restore page to earlier time without any error ?
2. would u plz give me a useful article to accomplish this ?
i found this link but it does not useful for me :
thanks in advance
September 13, 2009 at 2:19 pm
- with simple recovery you can only go to the previous full backup ( and if you have a differential backup, you can also apply that).
- how did you detect pages may have been corrupted (errormessages, ..??)
- did you perform dbcc checkdb ?
- did you check MS KB for bugs pointing to the symptoms you see ?
- what servicepack /hotfix is your instance on ? (@@version)
- You could try to restore the full backup to an alternate db-name and copy over the data you need ? Keep in mind that will be 4 days old !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 13, 2009 at 2:44 pm
Thanks for reply
how did you detect pages may have been corrupted (errormessages, ..??)
i found accidently this issue in windows event viewer that told me some page might be corrupted.
did you perform dbcc checkdb ?
the db size is 55Gb and when i run this command, it takes a long time, hence i canceled this command!
did you check MS KB for bugs pointing to the symptoms you see ?
i didn't any check kb, regarding to that error (page corruption) i'm looking for a way to restore page to an earlier time that corrected(in simple mode recovery).
what servicepack /hotfix is your instance on ? (@@version)
it's sql server 2000, but i don't know that it have sp or not. (i think it does not have any service pack)
You could try to restore the full backup to an alternate db-name and copy over the data you need ? Keep in mind that will be 4 days old !
how to accomplish this ?
My alternative way(regarding to that link which i posted in first thread) :
can i change recovery model to full ?
- then create a new log backup
- then restore page to an earlier backup
- then restore log backup that we just created.
Thanks
September 13, 2009 at 4:10 pm
I don't think that will work. My guess is your best bet is to check which object is affected and then see if it matters. If it's a nonclustered index, you probably can drop it and rebuild it.
If it's data, I think you should restore the backup to a new database, then copy that object back over. You might be out some changes, but if you save off the current db (bcp out), then you can figure out what the differences are.
This is a good reason why the full recovery mode should be used.
You also need to run dbcc checkdb. No matter how long it takes, you might have other corruption. Also be aware that corruption is usually hardware related, so you'll need to go through the hardware and be sure you don't have issues.
September 13, 2009 at 4:30 pm
Steve is absolutely right (despite the terrible shirt :-)) - and PAGE-level restore is only available in 2005 and later versions.
Paul
September 14, 2009 at 5:16 am
Steve is tright. you absolutely MUST run DBCC CHECKDB, and make sure you use the ALL_ERRORMSGS option and do NOT use the REPAIR_ALLOW_DATA_LOSS.
Sometimes Windows may report errors with SQL Server databases that do not really exist. You should rely on the results of DBCC CHECKDB to tell you if you really have database corruption.
The default in CHECKDB is to only give the first 200 error messagees, and if there are a number of problems you will not see all of them and may make the wrong decision on how to recover from the problems. The use of ALL_ERRORMSGS may give you a shedload of massages but you will see all the problems you have. It is also worth using NO_INFOMSGS so that the only messages you get relate to corruption, plus the normal termination message.
The use of REPAIR_REBUILD is OK, as there is no risk of loosing any data.
You should only use the REPAIR_ALLOW_DATA_LOSS after you have analysed the problems you have and are certain that doing a repair that looses data is the best or only way to get the system working again.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 14, 2009 at 5:49 am
- You can post the results of the DBCC checkdb if you want to.
- The main reason why you cannot just switch to full recovery, run a logbackup and perform the restore you intended to, is that with simple recovery, a transaction is marked to be "overwritable" as soon as it gets completed (commit/rollback), so sqlserver doesn't keep the needed history !
- As already stated by the other respondents, don't use the "allow dataloss" unless it is really really really a last resort. (return to the forum first 😉 )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 14, 2009 at 8:37 am
Paul White (9/13/2009)
Steve is absolutely right (despite the terrible shirt :-)) - and PAGE-level restore is only available in 2005 and later versions.Paul
And the database should be in full recovery recovery mode for page restores....
September 14, 2009 at 8:47 am
Terrible shirt?!?!
If you get errors from DBCC, I'd start a new thread in the data corruption thread and post it there.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply