locked
Restore Database .ndf files RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have a backup (.BAK file) which has bunch of .ndf files. I want to restore it on a database which has only .mdf and .ldf files. How to restore it? I am new to Sql server 2008 R2 and never restored .bak file which contains .ndf files...

    Thanks in advance...

    Thank you,

    Tuesday, October 23, 2012 2:54 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    I don't want to eliminate them. I want to restore them as well but don't know how to. Also the database where I want to restore the .bak only have .mdf and .ldf files.


    The target database is completely overwritten during the restore.  The number and size of the files after the restore will be the same as the original.  You can use the WITH MOVE and REPLACE options to relocate the files and replace the target database:

    RESTORE DATABASE TargetDatabase
FROM DISK='D:\Backups\SourceDatabase.bak'
WITH
	MOVE 'SourceDatabase' TO 'E:\DataFiles\SourceDatabase.mdf'
	,MOVE 'SourceDatabase_Data2' TO 'F:\DataFiles\SourceDatabase_Data2.ndf'
	,MOVE 'SourceDatabase_Data3' TO 'G:\DataFiles\SourceDatabase_Data3.ndf'
	,MOVE 'SourceDatabase_Log' TO 'L:\DataFiles\SourceDatabase_Log.ldf'
	,REPLACE;
ALTER AUTHORIZATION ON DATABASE::TargetDatabase TO sa;

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by scott_morris-ga Tuesday, October 23, 2012 12:57 PM
    • Marked as answer by Iric Wen Wednesday, October 31, 2012 8:33 AM
    Tuesday, October 23, 2012 3:58 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    .ndf are scondary files, any reason why u want to eliminate them?

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    Tuesday, October 23, 2012 3:05 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I don't want to eliminate them. I want to restore them as well but don't know how to. Also the database where I want to restore the .bak only have .mdf and .ldf files.

    Tuesday, October 23, 2012 3:10 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    I don't want to eliminate them. I want to restore them as well but don't know how to. Also the database where I want to restore the .bak only have .mdf and .ldf files.


    The target database is completely overwritten during the restore.  The number and size of the files after the restore will be the same as the original.  You can use the WITH MOVE and REPLACE options to relocate the files and replace the target database:

    RESTORE DATABASE TargetDatabase
FROM DISK='D:\Backups\SourceDatabase.bak'
WITH
	MOVE 'SourceDatabase' TO 'E:\DataFiles\SourceDatabase.mdf'
	,MOVE 'SourceDatabase_Data2' TO 'F:\DataFiles\SourceDatabase_Data2.ndf'
	,MOVE 'SourceDatabase_Data3' TO 'G:\DataFiles\SourceDatabase_Data3.ndf'
	,MOVE 'SourceDatabase_Log' TO 'L:\DataFiles\SourceDatabase_Log.ldf'
	,REPLACE;
ALTER AUTHORIZATION ON DATABASE::TargetDatabase TO sa;

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by scott_morris-ga Tuesday, October 23, 2012 12:57 PM
    • Marked as answer by Iric Wen Wednesday, October 31, 2012 8:33 AM
    Tuesday, October 23, 2012 3:58 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    2 things

    1. If this is the same DB which needs to be refreshed (even if this was refreshed earlier also from same DB's backup file) chances are someone now created a NDF file on source DB.

    --> Just refresh the database with simple command\GUI and select overwrite the existing DB.

    2. May be this is not the DB which should be refreshed by this backup file- double check with the person who has better understanding of the environment.

    Sarabpreet Singh Anand

    Blog , Personal website

    This posting is provided , "AS IS" with no warranties, and confers no rights.

    Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, October 23, 2012 2:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    In the moment you want to restore the database, verify the path of the database where you want to restore them, then change the path before the *.ndf file, and the restore will be successfull
    Tuesday, January 14, 2014 10:09 PM