0

I have an already created mdf file which was created by sql server 2008.But when I attach this mdf file to sql server 2012 it asks me to add necessary full text catalogs. I have no idea what to do.Please help me.

HopelessN00b
  • 53,795
  • 33
  • 135
  • 209
karthik
  • 1
  • 1
  • Do you have the ldf and any ndf files? Is full text installed on the 2012 server? – Katherine Villyard Feb 22 '15 at 14:22
  • 1
    Or for that matter is the sql server 2008 still running? The best way to safely migrate a database is [using a backup](http://dba.stackexchange.com/questions/61810/what-are-the-risks-of-copying-mdf-ldf-files-without-detaching-the-db-first), not the mdf/ldf files. – Reaces Feb 22 '15 at 19:40
  • @Reaces I suspected but didn't want to say until I confirmed: sp_attach_single_file_db doesn't work if you have an ndf, so if karthik has an ndf he also needs the ldf. https://msdn.microsoft.com/en-us/library/ms174385.aspx – Katherine Villyard Feb 23 '15 at 01:29
  • @Reaces sp_attach_single_db works fine with a single mdf and no ldf. I'm saying that it won't work if he has an mdf, one or more ndfs, and a missing ldf. I got that link by clicking in this guy's article, I suspect. https://sqlnotesfromtheunderground.wordpress.com/2013/11/12/attaching-a-database-without-the-ldf-file/ Let me rephrase. I have a database with an mdf, five ndfs, and an ldf. Can you give me the syntax to attach everything except the ldf with the sp_attach_ *single* _db procedure? – Katherine Villyard Feb 23 '15 at 13:49
  • @Reaces sp_attach_db accepts up to 16 files. https://msdn.microsoft.com/en-us/library/ms179877.aspx – Katherine Villyard Feb 23 '15 at 14:23
  • @KatherineVillyard I thought we were talking about `sp_attach_single_file_db`. At any rate, I'd use create database with attach_rebuild_log. ;) Also I think I'm mixing up terms here! In which case I apologise! But we're leaving the topic quite rapidly here. – Reaces Feb 23 '15 at 14:29

1 Answers1

1

It's hard to say based on the information you've provided, but it sounds like you've used the sp_attach_single_file_db procedure to attach an mdf, and you may be missing one or more ndfs.

People talk about mdf and ldf files a lot in SQL Server, and don't often mention ndfs. This is because they're a lot less common. I have about 8000 databases and only one of them has ndf files. It's a vendor database that uses them to store (surprise!) full-text catalogs.

Anyway, the safest way to move a database is to back it up on the old server and restore it onto the new server, like Reaces pointed out in the comments. If you're determined to use detach/attach, you'll need to make sure you have all the relevant files, including any ndf files.

Katherine Villyard
  • 18,550
  • 4
  • 37
  • 59
  • I'd like to add that another reason .ndf is so rare is because it's not an enforced standard. And as such people who don't generally use databases a lot but want to create a secondary data file tend to call it by different names I've had a customer where all his 'ndf' files were actually called mdf1, mdf2, ... – Reaces Feb 23 '15 at 14:38
  • I've heard that mdf stands for "main data file," ldf for "log data file," and ndf for "non-main data file." I'm not sure if that's an urban legend or not, although I seem to recall a video with a blushing MS person admitting that was what it stood for. :) – Katherine Villyard Feb 23 '15 at 15:05
  • 1
    I always preferred the Southern American explanation. `Why son, that's just 'nother data file!` – Reaces Feb 23 '15 at 15:08
  • Hahaha! I love that *especially* because I'm in Atlanta! – Katherine Villyard Feb 23 '15 at 15:10