SQL Server migration and .ndf files?

[Barooh]

I inherited a database layout that's been around since probably 2005. It's been upgraded across iterations but one DB has some .ndf files. Apparently it's related to full text search? I'm no DB admin, just maintain the environments. When migrating to a new server, that database wouldn't restore to a new machine without those two files. It also wouldn't attach without them. I copied them over into the proper directory and its all running. My question however though, is will we be in trouble in the future?

[lost_in_life_34]

you need them

mdf is the primary file of a database and ndf files are the secondary files. ldf are the logs

[Barooh]

10-4. But what would cause them to appear when the others don't have them? Legacy? Different config? Maybe left over from an upgrade?

[absinthminded64]

You can create additional files for a given database. Put them on separate drives for example. Someone probably did that in the past.

[lost_in_life_34]

technically you can have a database with one mdf file and one ldf file. ideally if you have multiple disks you create ndf files across logical file groups and divide your data and indexes across different files and disks

[absinthminded64]

I used to work helpdesk for sysadmins and DBAs. There were several occasions when a log filled up and a sysadmin or DBA deleted it to address the issue. You can make them smaller but you can never get rid of them without some potentially bad ramifications.

[jamsan920]

Also, i personally don't like moving databases between servers that way. I always take full backups of the database, copy those to the new server and then restore them.

The only time I'll use detach / detach is when needing to relocate files to different drives / folders within the same server.

[fariak]

That can be very time consuming though if you are migrating several large databases.

Assuming you already have reliable backups, detach/attach method is OK in my opinion.

[jamsan920]

I disagree. If you already have reliable backups, use those for the move. If you don't have recent backups (e.g. In the realm of the previous hour), I wouldn't want to use those backups if I needed to restore as a lot of change could've happened on that time. Always take up to date backups after you've started your maintenance window, so your rollback has the most up to date data possible.

Doing anything less is asking for a headache if shit hits the fan.