November 6, 2010 at 12:22 am
Advantages of .ndf file.
In a database one file is missed ,They have no backup.how to solve it
Thanks in advance
November 6, 2010 at 12:48 pm
sankarkot2007 (11/6/2010)
Advantages of .ndf file.In a database one file is missed ,They have no backup.how to solve it
Throw the database away and start with a clean slate.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 6, 2010 at 1:32 pm
Can you please be more specific about the scenario?
Is this a file in the primary filegroup? Or is it a file a file in a secondary filegroup?
What edition of SQL?
Is the database attached to an instance or are you trying to attach it?
How did the file 'go missing'?
The more information you can give, the greater the chance that we might be able to fix this.
p.s. How come there are no backups? Are you absolutely sure there are no backups at all? Query the msdb backup tables (sysbackupset and related) to see if maybe someone took a backup sometime.
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
November 6, 2010 at 1:32 pm
da-zero (11/6/2010)
Throw the database away and start with a clean slate.
Not funny in a disaster situation.
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
November 6, 2010 at 8:48 pm
GilaMonster (11/6/2010)
p.s. How come there are no backups? Are you absolutely sure there are no backups at all? Query the msdb backup tables (sysbackupset and related) to see if maybe someone took a backup sometime.
Something like this:
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
b.recovery_model_desc as 'Recovery Model',
case
when state_desc <> 'ONLINE' then state_desc -- Alert that DB might be ReadOnly, Offline etc...
else ' '
end as 'DB Status'
FROM master.sys.databases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name --AND A.type = 'D'
where B.name = 'MyDatabase'
GROUP BY B.name , a.type, b.recovery_model_desc, state_desc
ORDER BY B.name , LastBackupDate desc,a.type, b.recovery_model_desc, state_desc
---
November 7, 2010 at 6:10 am
GilaMonster (11/6/2010)
da-zero (11/6/2010)
Throw the database away and start with a clean slate.Not funny in a disaster situation.
Absolutely true.
But I'm pretty confident this is not a disaster situation, but an interview question that the original poster received.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 10, 2010 at 7:54 am
da-zero (11/7/2010)
GilaMonster (11/6/2010)
da-zero (11/6/2010)
Throw the database away and start with a clean slate.Not funny in a disaster situation.
Absolutely true.
But I'm pretty confident this is not a disaster situation, but an interview question that the original poster received.
Pop quiz, hotshot. There's a bomb in an ndf file. Once the ndf is deleted accidentally, the bomb is armed. If the database starts up, it blows up. What do you do? What do you do?
I tell ya what you do. Hope that it only contains indexes. Otherwise, use a deleted file retrieval program and hope that you can still find remnants on the server to restore the file. *had to do this before for a client
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply