is possiblity to remove the .NDF file (secoundary ) during restore of the backup?

  • Hi,

    Is there possiblity to remove the .NDF file (secoundary file ) during restore of the backup file as we can remove the transaction log file and what exactly the effect if we do so .

    case 1 (model is simple recovery)

    no log files are reuired as we are going for normal restore process

    And When we remove the logfile during the restore process of the database what exactly the effect will be on the restored database is there any chance of missing the data

    Case 2 what the same effect if it is full recovery model.

    Thanks

  • No. A restore puts the DB back as it was at the time of backup. You can't remove log files during a restore either, they're required for restoring the DB to a consistent state.

    It's possible to restore just the files in the primary filegroup, but that doesn't remove the secondary data file, SQL just marks it as offline and unavailable.

    Restore the DB with all files, then take the necessary steps to remove the data file.

    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
  • Regarding the restoration with no logfile i have try and my database is restored and is working fine.

    and its creating from itself the log file even though i removed during restore process.

    Might be it will lost some date in full recovery model case if we are removing the logfile from restoration.

    What the steps to remove the ndf file after the restoration is done ...

  • How are you 'restoring'?

    In a restore of a backup (RESTORE DATABASE) it is not possible to remove a log file, or any other file for that matter.

    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
  • From enterprise manager if you go for restore process and when you will click the tab "option"

    you can find the list of files and location of getting restore so delete the location of logfile and and file name ..

    it will work

  • Enterprise manager? SQL 2000?

    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
  • I don't have SQL 2000 and I don't have Enterprise manager, but I suspect all that's doing is restoring the log with default location. You cannot delete a log file during restore, the files are needed. You cannot delete a file during restore.

    Check the SQL that the dialog is actually running.

    As for removing the NDF, is it part of another filegroup or is it in its own 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • no is of seperate file group

  • Then you need to move all the tables/indexes from that filegroup somewhere else (rebuild the clustered index on the new filegroup), then run ALTER DATABASE DROP to get rid of the file.

    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
  • Can i have the steps and query to accomplish the above mention tasks

    Thanks

  • To move indexes/table to another filegroup

    CREATE [CLUSTERED] INDEX ... WITH DROP_EXISTING ON <other filegroup name>

    To remove the file once empty

    ALTER DATABASE ... REMOVE FILE ...

    Read up in books Online for exact syntax, I don't have it memorised either.

    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
  • you can refer http://msdn.microsoft.com/en-us/library/ms175905.aspx for moving existing indexes to other filegroups..

  • Thanks to all 🙂

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply