0

I have been moving some databases from SQL 2008 to SQL 2012 using detach and attach method. I correctly map up all the files and the physical and logical names appear correctly in the properties of the database.

However if I run sys.master_files I can see that all my files have been given the same file name in the Name column of the DMV and this is making our alerting system go crazy.

Has anyone seen this before or know how I can fix it?

Thanks

Improve this question

1 Answer 1

Reset to default
2

Well, for one, you really shouldn't be moving database files around using detach/attach:

The problem probably happened because you reused MDF/LDF files for new databases with different names - the files keep the properties of the original database even if the new database has a different name and the files have different filenames/locations.

But this is an easy "problem" to fix:

ALTER DATABASE dbname MODIFY FILE (NAME = N'old_name', NEWNAME = N'new_name');

(Another way to fix it is to stop your alerting system from even looking at this, never mind complaining about it - other than OCD, there is no reason why two different databases can't have the same logical file names - they're unique per database, not per instance.)

Improve this answer
4
  • Interestingly when using the ALTER DATABASE command you have to put in the name of the file you want the name to be in both the NAME and NEWNAME fields. This feels like a bug to me. Further to that the names are correct on the source server so it must be picking up this other name from somewhere just I don't know where.
    – Tom
    Commented Jun 10, 2015 at 14:08
  • Why is it a bug? If you have multiple files in your database, and you don't specify the name of the file you want to rename, which one should it pick? Also "correct on the source server" means it was created explicitly with those names are the database happened to be named the same thing that matches the files. The name of the files is stored in the metadata and it doesn't matter what you rename the database to, by default it will get created with its original file names, not anything that has anything to do with the name you restored/attached as.
    – Aaron Bertrand
    Commented Jun 10, 2015 at 16:59
  • I don't follow, let me explain again. In sys.master_files on my source server the name of the files might be Tom1.mdf and Tom1.ldf. When I move them to my new server it would get renamed to Tom1.mdf and Aaron.ldf. Now this would happen for every database so in sys.master_files I would see lots of different mdf names, but the same ldf name for every database. As a side note if I looked at the logical names in the GUI then it would look the same as it was on the source server. Are you saying this is expected behaviour?
    – Tom
    Commented Jun 11, 2015 at 13:52
  • I don't understand where Aaron.ldf would come from but it either came from the source server, the script you used to restore / attach, or perhaps inherited from model depending on the way the file was restored / attached. Anyway, as my answer explains, it's very easy to fix that if it's a problem.
    – Aaron Bertrand
    Commented Jun 11, 2015 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.