Recovery question??

• rambilla4

  SSCrazy Eights

  Points: 8715

  More actions

  July 19, 2009 at 12:51 am

  #212333

  Hi,

  We have sql server 2005 EE edtiton x64. I have a general question regarding the recovery.

  I have database PROD, with 3 data files and 2 log files as below

  Prod_1.mdf, Prod_2.ndf in file group 1 and Prod_3.ndf in file group2 and prod_1.ldf, prod_2.ldf.

  and my prod_2.ndf is got corrupted:

  Can we restore ONLY prod_2.ndf file from the backup file PROD.bak??

  If yes please explain me how can we do that

  thanks

• ps.

  One Orange Chip

  Points: 29422

  More actions

  July 19, 2009 at 1:06 am

  #1026626

  rambilla4 (7/19/2009)

  ---

  Prod_1.mdf, Prod_2.ndf in file group 1 and Prod_3.ndf in file group2 and prod_1.ldf, prod_2.ldf.

  and my prod_2.ndf is got corrupted:

  Can we restore ONLY prod_2.ndf file from the backup file PROD.bak??

  If yes please explain me how can we do that

  thanks

  If you have filegroup backups, you can restore the affected filegroup along with the primary filegroup. In your case since the affected file is a part of primary filegroup, you can only restore the primary filegroup backup(provided u were doing filegroup backups)

  Or else you'll need to restore the entire database.

  I'd also suggest you run dbcc checkdb to see the amount of data corruption. In some cases you can do page level restores from your past backups just in case number of affected pages are less.

  EDIT - corrected word.

  ---

  
  Pradeep Singh

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 19, 2009 at 2:31 am

  #1026628

  rambilla4 (7/19/2009)

  ---

  Can we restore ONLY prod_2.ndf file from the backup file PROD.bak??

  Providing that you have log backups from the time of that full backup up until present, you should be able to.

  http://msdn.microsoft.com/en-us/library/ms191253(SQL.90).aspx

  http://msdn.microsoft.com/en-us/library/aa337540(SQL.90).aspx

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass