Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to restore .ndf file in database SQL 2005 Expand / Collapse
Author
Message
Posted Sunday, August 10, 2008 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 25, 2010 12:45 AM
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

Post #549840
Posted Sunday, August 10, 2008 9:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 4:00 PM
Points: 2,038, Visits: 1,663
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
Post #549868
Posted Sunday, August 10, 2008 5:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 33,051, Visits: 15,160
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
Post #549933
Posted Sunday, August 10, 2008 9:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 25, 2010 12:45 AM
Points: 7, Visits: 24
yea i stop the service and move .ndf file from one drive to other drive
Post #549970
Posted Sunday, August 10, 2008 10:09 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 4:00 PM
Points: 2,038, Visits: 1,663
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
Post #549981
Posted Monday, August 11, 2008 9:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 25, 2010 12:45 AM
Points: 7, Visits: 24
thanks to all, yea i have a last backup of the database and it restore.
Post #550721
Posted Monday, August 11, 2008 9:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 33,051, Visits: 15,160
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
Post #550723
Posted Tuesday, April 29, 2014 4:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 8:13 AM
Points: 3, Visits: 5
@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.
Post #1565831
Posted Tuesday, April 29, 2014 4:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
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

Post #1565832
Posted Tuesday, April 29, 2014 4:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 8:13 AM
Points: 3, Visits: 5
@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.**
Post #1565839
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse