August 10, 2008 at 4:51 am
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
August 10, 2008 at 9:59 am
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
August 10, 2008 at 5:08 pm
Do you have a backup from SQL Server, or did you stop the service and copy the .ndf at some point?
August 10, 2008 at 9:34 pm
yea i stop the service and move .ndf file from one drive to other drive
August 10, 2008 at 10:09 pm
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
August 11, 2008 at 9:49 pm
thanks to all, yea i have a last backup of the database and it restore.
August 11, 2008 at 9:53 pm
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.
April 29, 2014 at 4:09 am
@steve-2 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.
April 29, 2014 at 4:10 am
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
April 29, 2014 at 4:36 am
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.**
April 29, 2014 at 5:14 am
You never need to create a dummy database before restoring. The restore process creates the database. Creating an empty database and then restoring over it is just a waste of time.
If the database which the backup was created from had an ndf file, then the backup would include that and the restore would recreate it. There's no silly tricks needed.
If your restore isn't creating the ndfs, then you may be looking at the wrong backup. RESTORE HEADERONLY and RESTORE FILELISTONLY to see what is inside the backup
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
April 29, 2014 at 8:13 am
Hi GilaMonster,
You may think that the way i resolved this issue is totally stupid 😎 but please hear me out
First of, i'm using " SQL Server Management Studio 2005 " to take backup and restore the database. I'm pretty sure that my backup has all the mdf,ldf, and ndf files. You told that i don't need to create a new db to restore the database in the target server. I tried, but no help.
Here is how i solved it.
As i told in the earlier reply, i tried to create a dummy .ndf file. I achieved it by opening new query in SQL Server Management Studio and saved it as filename.ndf ( the file name which was prompted while restoring ). This did the magic... :hehe: restoration completed like a charm.
I'm pretty sure that this is NOT the right way to solve this issue. But this might help some one is resolving the issue.
Any way thanks for your help.
April 29, 2014 at 8:55 am
????
To restore a database, right click the databases folder (object explorer), select Restore database. Select the Device radio button. Locate the backup, select which backupset in the file you want to restore (if there's more than one), Type in a database name (the database name dropdown can be typed into). Go to the second tab, specify the new locations for files if applicable.
Click OK.
That's it, no creating dummy databases, no creating dummy files, they're not needed and doing so can lead to an incorrect perception as to what's happening and what's required.
Or, if you prefer,
RESTORE DATABASE <new database name> FROM DISK = <location of backup>
WITH
MOVE <logical name of the mdf file> TO <new location and file name for the mdf>,
MOVE <logical name of an ndf file> TO <new location and file name for the ndf>,
.. repeat for other ndf files ...
MOVE <logical name of the log file> TO <new location for the log 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
June 20, 2014 at 2:26 am
Hi subhash,
Get logical file names as follows
restore filelistonly from disk='your backup path\filename.bak'
then restore database as follows,
restore database database_name from disk='your backup path\filename.bak'
with
move 'Logical_name for mdf file' to 'Path where you want to create .mdf file',
move 'Logical_name for ndf file' to 'Path where you want to create .ndf file',
move 'Logical_name for ldf file' to 'Path where you want to create .ldf file',
stats=1
Note:- Depends on number of your .ndf file use move to part.
June 21, 2014 at 8:59 am
One of my database has ndf file but its not there in the other one.No idea why this is happening.Is anything sruiously wrong with one of the database.
🙂
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply