April 13, 2022 at 7:59 am
Hi Experts,
Recently my database is crashed n went into non-recovery mode, i have tried to recover it but no luck, so i have offline the database and detached it and created a new database of the same name. although my database is detached and i have 31 ndf files , and i want to try a luck into all the 31 files, can i use/restore that files and use that data into another table
Old NDF File Name as "DPDL_01" for table Name is "DeviceParsedDataLog_01"
CREATE TABLE [dbo].[DeviceParsedDataLog_01](
[DeviceRegistrationId] [varchar](20) NOT NULL,
[GPSSignalStatus] [varchar](10) NOT NULL,
[ReceivedDateTime] [datetime] NOT NULL,
[Speed_KmPerHour] [int] NULL,
[Heading_Degrees] [int] NULL,
[EventId] [int] NULL,
[Latitude] [numeric](9, 6) NULL,
[Longitude] [numeric](9, 6) NULL
) ON [DPDL_01]
GO
how do i use the old ndf files to new tables?
can someone help me out.. for a data recovering...
Thanks
Mohamad Feroz Patel
Patel Mohamad
April 13, 2022 at 1:12 pm
You haven't mentioned the mdf file (primary data file). The corruption is in your mdf and/or one or more of the ndf files.
You restore from backups. Do you have recent backups? If not, why?
If not, some of our experts here may be able to advise on how you might be able to recover some of your data.
31 secondary data files?!? How big is your database? Was each of those on a separate drive/spindle? If not, why do you have 31 secondary database files?
April 13, 2022 at 1:27 pm
I hope your backups are in place !
If so, try to make an extra log-backup.
Secondly check your disk subsystem !
Only if that is ok, you are to restore the affected database(s).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2022 at 2:35 pm
If you have MDF & NDF files, you can try using ATTACH to recover them. But, as others have already said, if your database is corrupt, it's in those exact same files, so recovery through ATTACH is unlikely to work. You haven't mentioned backups, but that's probably going to be your best bet.
There are third party solutions that might (emphasis) enable you to read data from the files directly. Here's one example. No idea if that's going to work for you or not. Probably not.
This is why backups are so vitally important.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 14, 2022 at 9:30 am
Hi Experts,
Thanks for your valuable time and suggestion given by you, this is Copy of Main Database on Daily Bases data is inserted and unwanted data is deleted,
We have splited the data in Main Monthly Database and 31 NDF Tables
1 NDF File contains the data of Day -01 or 02 or 03 or 04 like 31 - "Jan,Feb,Mar,Apr,May" 5 Months data in one table as soon as June Arrives the Jan Months day data is deleted remains the last 5 months data. this is how we have balance the data into 31 Tables/NDF Files.
so i have not taken a backup of the database, because it is not required as my Data balance plan is , balancing the data according.
The NDF file size is around 10-11 GB each, where as my MDF File is is hardly 100 mb in size. To fetch the data fast and accurate, we have split the one table into 31 Table(s), that user can access the data easily without any delay on my portal,
I have tried the following code to to recover the database from suspect mode.
USE master
GO
ALTER DATABASE DBN SET EMERGENCY
GO
DBCC CHECKDB (DBN)
GO
ALTER DATABASE DBN SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (DBN, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE DBN SET MULTI_USER
GO
after setting to database to emergency mode the dbcheck did not perform well and given database corrupt error, so i have offline the database and detached it. and created new database with same name to smooth my process for my portal follow.
so want to restore the NDF files as a new table.
Hope, you experts got my Concern and steps followed to recover the database...
thanks and waiting new suggestions.
Patel Mohamad
April 14, 2022 at 1:05 pm
"so i have not taken a backup of the database, because it is not required as my Data balance plan is , balancing the data according."
And now you have no data to balance. You have learned, late in the game, a very critical and expensive lesson. Database backups are critical if your data matters (if it didn't, you presumably wouldn't be scrambling to recover it). Backups are the only rational strategy for database disaster recovery -- e.g., when a database becomes corrupt, when a server crashes, when disks fail, etc. (There are some VM backup strategies out there that seem to work, but most DBAs tend to trust database backups to ensure transactional integrity point-of-time recovery capabilities.
You, or a very expensive data recovery service, may be able to recover some of your data. Unfortunately, your plethora of secondary files probably makes this an even more complex and expensive quest.
If you are able to recover or recreate your database, do nothing else until you set up a backup plan (recommend Ola Hallengren scripts) that includes backing up to different disks than your data drives, and copying the backups offsite. Look at this article describing Recovery Point Objective (how much data can you afford to lose), Recovery Time Objective (how long is it OK for the database to be down), and Service Level Agreement (what contractual obligations do you have), & base the decision on those, as well as the nature of your data flow. If you only bulk load data nightly with no other transactions, there may be no need for log backups. But if data changes frequently, you may need frequent log backups as well as periodic (e.g., weekly) full backups and (e.g., daily) differential backups.
I wish you well, but please never manage a database again without backups, for your sake and those that depend on you.
April 14, 2022 at 1:13 pm
Do you have backups of your main database you can use to recover and load into your "copy" database ?
"...this is Copy of Main Database..."
I don't understand why you have so many NDF files.
Perhaps backups are required after all ?
"...so i have not taken a backup of the database, because it is not required..."
Sometimes backups are not required. For example a testing or reporting database that can easily be refreshed from production. Even then, there may be stored procedures, indexes, views that were created and could be lost.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply