June 9, 2012 at 9:33 am
Hi My Database has crashed and I have lost only 2 ndf files out of 9 ..is there a Possible way to recover just those 2 files and also any way to decrease the downtime .
Please Help
June 9, 2012 at 10:51 am
I hope you have a good backup. That is the the best way to recover your database.
June 9, 2012 at 10:59 am
Yes Lynn I do have the back up ..but My question is is there a way to just recover the certain 2 files without doing all the restoration process of all the old backups ...??
I mean what would be the Process...Please Advise..
June 9, 2012 at 11:07 am
Based on what you have posted, no. You will need to restore the database.
June 9, 2012 at 11:24 am
OK thanks for your Answer.But as you been very precise I would like to elaborate the Answer Kindly Confirm and Correct me in Case I am Wrong somewhere kindly correct me
In case any of the File-group\file Crashes ..The only option to Recover a Database is to Get it completely restore using the OLD BACKS UPS
Right ??
June 9, 2012 at 12:49 pm
I would start making the current log-backup of that database !
Then it should be able to get you to the current state, starting from your previous full backup.
If you can, I strongly advise to make an inventory of the objects that would reside on the lost filegroups.
If it's only non clustered indexes you still have all your data.
What size are we talking about and what is your db topology ?
- engine @@version
- partitioning ?
- RTO ?
- RPO ( Having created your last log backup, that shouldn't be an issue )
Prepare your action.
IF you have time and space , restore to an alternate db to be sure your restore of that db will work !
Yes, that takes extra time, but at least you don't lose more data if it doesn't work ( e.g. corrupt backup)
and leaves you extra options to save whatever data is left and available.
restore the full db with NOrecovery
then apply the log backups in sequence ! ( It won't let you do mistakes here because the backup contains db syncpoints that must match the current state of the db)
Keep in mind to apply them using With NOrecovery, until you have restored your final log bu.
( I explicitly apply the recovery of all logs using NOrecovery and only manually use restore db with recovery after having double checked I applied all available log bu)
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
June 9, 2012 at 3:15 pm
If you have every single file in the primary filegroup, you should be able to bring the DB online. Of course, any filegroups with missing files will be unavailable. If any file in Primary is missing, the database cannot be brought online
Regardless, the only way you're going to recover completely is by restoring backups.
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
June 10, 2012 at 12:40 am
Gila lets take a Scenerio :
There are 3 file groups for My db
Primary
Secondary
Log
Point 1.Now in case Anything goes WRONG with my Primary File Group ..then we need to restore the complete Database Backups to bring it online
Point2.In case anything goes wrong with my secondary Filegroup....can you help What would be the Steps to Recover files..In Case We can
Point 3.Also If we Lost any of the log file ..What would be the Point of Action with Steps .
Please Help it would help in understanding recover the DB in any Crisis Situation for all the DBA's
Appreciate your Support .:)
June 10, 2012 at 5:08 am
Jai-SQL DBA (6/10/2012)
Point2.In case anything goes wrong with my secondary Filegroup....can you help What would be the Steps to Recover files..In Case We can
Restore from backup if you need the files back. Depending on the edition of SQL, the database may remain online or may not.
Point 3.Also If we Lost any of the log file ..What would be the Point of Action with Steps .
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
p.s. Log files aren't in a filegroup.
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
June 10, 2012 at 9:16 am
Jai-SQL DBA (6/10/2012)
Gila lets take a Scenerio :There are 3 file groups for My db
Primary
Secondary
Log
are all talking scenario here or the actual configuration of your database?
As Gail pointed out logs have no filegroup, so where is the figure 3 coming from?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 10, 2012 at 10:05 am
Perry Whittle (6/10/2012)
are all talking scenario here or the actual configuration of your database?
I suspect the latest question is just a theoretical config for an broad question.
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
June 10, 2012 at 11:45 am
Thanx for you responses Gail..you are a Great Help Dude... I still need to know the Steps \Queries how to recover the Files from Point 2.
June 10, 2012 at 11:57 am
here are some other refs ( Gail showed you _the_ one 😉 )
- see topic "Restoring a Filegroup From a Full Backup" at http://sqlserverpedia.com/wiki/Restoring_File/Filegroup_Backups
- Of course there is always Books Online !
http://msdn.microsoft.com/en-us/library/ms186858(v=sql.105).aspx
Prepare your action
If you want to, produce your scenario and have it checked in your SSC thread :w00t:
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
June 10, 2012 at 12:15 pm
Jai-SQL DBA (6/10/2012)
I still need to know the Steps \Queries how to recover the Files from Point 2.
Really? What was unclear about what I said?
GilaMonster (6/10/2012)
Restore from backup if you need the files back. Depending on the edition of SQL, the database may remain online or may not.
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
June 15, 2012 at 7:25 am
In order to recover specific files or filegroups that are lost, you first have to"
1. Back up the tail of the log
2. Restore the files or filegroups that are missing from a backup
3. Restore all log backups since the backup you used to restore the missing files/filegroups to bring those files/filegroups current with the rest of the database
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply