December 18, 2008 at 11:22 am
Good Afternoon,
Can someone advice me as to how I might fix this? Problem description:
I detached nine DB's and then copied the files that were located in original server's "SQLServerData" directory. This process has been done many times before. I then copied this MDF and LDF files for each DB to a new server's "SQLServerData" directory and then tried to attached them. Two of the nine DB's I moved attached OK. But seven of them are looking for three NDF files that were located under the
"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FTData\ftrow_Baxter AgileDoc Gateway_tkObjects.ndf"
There are three files like the above for each of the seven DB's, ie:
ftrow_Baxter AgileDoc Gateway_tkObjects.ndf
ftrow_Baxter AgileDoc Gateway_tkOLEData.ndf
ftrow_Baxter AgileDoc Gateway_cDMS.ndf
What are these, why were these files not part of the MDB / LDF files and can I still attached the DB?
The original server has been rebuilt.
Is there a solution to this problem?
TIA and Regards,
Paul
December 18, 2008 at 11:54 am
Did you happen to take a full backup of each of the databases prior to detaching them to move them to a new server? If you no longer have access to the secondary datafiles (the .ndf files) the only recourse you have at this time is to restore from a backup file.
December 18, 2008 at 12:00 pm
Thank you Lynn for your reply,
We have a daily backup routine that runs each day, on the active DBs
Regards,
Paul
December 18, 2008 at 1:28 pm
Is it possible to attach the DB even though it is looking for three NDF files?
Regards,
Paul.
December 18, 2008 at 1:35 pm
paul.kuczwara (12/18/2008)
Is it possible to attach the DB even though it is looking for three NDF files?
No. Those are secondary data files, ie they contain part of the data in the database. If you don't have those, you'll have to restore from backup
If it were possible to attach without those it would mean that you're only attaching a portion of the DB.
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
December 18, 2008 at 2:17 pm
Thank you Gail,
I am new to SQL and all of the internals of "how", I guess my question is how are these files created? And why are they not backed up to the MDB and LDF location directory?
Regards,
Paul
December 18, 2008 at 2:29 pm
They're created by someone adding a second (or third or forth, ...) data file to the database. Only the primary file has the extension mdf. All other data files are .ndf (Secondary data file)
I don't know what you mean by backed up.
The ndf will be in whatever directory the person who created it put it. It may be in the same dir as the mdf, it may not.
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
December 18, 2008 at 2:36 pm
Good Afternoon All,
I am a nubie at SQL, and SQL below was written by someone else, while I can work with SQL to a limited degree. SQL like this I am not that sure of. Can someone advise if this does a FULL backup of each DB?
SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[tkSecurityContext]') AND type in (N'U'))" + vbNewLine + "truncate table [tkSecurityContext]" + vbNewLine + "BACKUP DATABASE [" + oNode.InnerText + "] TO DISK = N'" + dbPath + dbFileName + ".bak' WITH NOFORMAT, NOINIT, NAME = N'" + dbFileName + "', SKIP, REWIND, NOUNLOAD, STATS = 10"
Regards,
Paul
December 18, 2008 at 3:45 pm
Backup database youDatabaseName
To Disk ='YourPath'
does take complete backup.
Refer to BOL/MSDN for BACKUP for various options...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply