Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to restore .ndf file in database SQL 2005


how to restore .ndf file in database SQL 2005

Author
Message
Ahmer Ali
Ahmer Ali
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
hi all,

i have mistakenly deleted .ndf file of the database and but i have a backup of that file any one help my how to restore .ndf file in my database or any other suggestion so that my database can worked again right now by database is currpt its not working properly
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 1714
As long as you're in the FULL recovery model, this should be simple.

1) set the file to be offline using ALTER DATABASE dbname MODIFY FILE
2) restore the initial image of the file from your last full backup, specifying the name of the file you want to restore:
RESTORE DATABASE dbname FILE = 'full pathname and filename of the file'
FROM DISK = .... and so on

3) restore all log backups you currently have since the last full backup, using WITH NORECOVERY. Only log records pertaining to that file will be restored.
4) take a backup of the 'tail of the log' using
BACKUP LOG dbname TO DISK = .... WITH NO_TRUNCATE
5) restore the 'tail of the log' backup using WITH NORECOVERY
6) complete the operation using RESTORE DATABASE dbname WITH RECOVERY

This brings the file right up to the same point in time as the rest of the database.

Let me know how you get on.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36266 Visits: 18752
Do you have a backup from SQL Server, or did you stop the service and copy the .ndf at some point?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ahmer Ali
Ahmer Ali
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
yea i stop the service and move .ndf file from one drive to other drive
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 1714
Ah - forget everything I said above then. Do you have any SQL Server backups at all? And old is the .ndf copy you have?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Ahmer Ali
Ahmer Ali
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 24
thanks to all, yea i have a last backup of the database and it restore.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36266 Visits: 18752
You should not stop the service to do backups. SQL Server does native online backups while the service is running, and that is the way you should back up your data.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
subhash.ct32
subhash.ct32
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
@Steve Jones
Hi,
Could you please be more specific on how to backup .ndf file. Today i ran into the same kind of issue. I took the backup of the db through " SQL Server Management Studio Express 2005 " and tried to move it to a new server. I created a empty database in the target server with the database name ( which created .mdb and .ldb file alone ) and tried to restore it. Now my problem is that, how can i restore a .ndf file? I taught of taking the files by stopping the service. but you told it's not a good way. So please suggest me a way to restore the database.

Thanks in advance.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47351 Visits: 44392
An .ndf will be part of a full database backup (BACKUP DATABASE ...) and will be restored as part of a full database restore (RESTORE DATABASE)

p.s. Please in future post new questions in a new thread. Thanks.


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


subhash.ct32
subhash.ct32
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
@GilaMonster

I understand that. But in the target server without creating an empty database i wont be able to restore the database in the target server. when i create an empty database .mdf and .ldf files will be created by default. But my db in the source server has .ndf file. My backup has all the .ndf files, but in the target server those files are not there. Is there a way to create a dummy .ndf file and restore it with the contents form the db backup?

**thanks for the advice. will create new post in future.**
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search