'Logical file name "MyDB" is already in use' Even though the database doesn't exist I recently was given a SAN with a couple of RAID arrays to apply to my database server to increase performance. I have (and will only ever have) one user database called DDAS, in addition to the system databases that install along with SQL Server. 

My intent was to have DDAS pointing to its own drive, tempdb pointing to its own drive and transaction logs pointing to their own drive. The user account running SQL Server has Full Control access to all the drives, I made sure of it before doing anything.

I successfully moved tempdb using the following SQL statements:

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = N'F:\tempdb.mdf')
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = N'F:\templog.ldf')


I restarted SQL Server, voila, tempdb is relocated (can I safely delete the old tempdb files from their original location? Maybe that's another question...)

Now, because DDAS is still in development, I was able to delete all data from all the tables before doing anything to move it to its own drive. So now the file is ~5MB in size. I perform the same statements using the DDAS files:

ALTER DATABASE [DDAS] MODIFY FILE ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf')
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'DDAS_log', FILENAME = N'G:\DDAS_log.ldf')


I restart SQL Server and it doesn't work... I get DDAS (Recovery Pending) on the database in Management Studio indefinitely. I deleted the database completely because I have a script that will create all the objects etc... intending to just try to re-create it on the drive it is supposed to be on. I attempted to create the database with the following statement:

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DDAS')
BEGIN
    CREATE DATABASE [DDAS]
    CONTAINMENT = NONE
    ON  PRIMARY 
        ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf' , SIZE = 1024000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024000KB  ) 
    LOG ON      
        ( NAME = N'DDAS', FILENAME = N'G:\DDAS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    END
GO


but I get this error now: 



I used this query to list file names being used by SQL Server:

SELECT DB_NAME(database_id) AS DatabaseName, 
       name AS LogicalFileName, 
       physical_name AS PhysicalFileName 
FROM sys.master_files AS mf


and got this result:



How is this possible? I tried restarting SQL Server a number of times to no avail. I also checked that the original file on the C:\ drive that DDAS was using has been deleted, as well as its transaction log. There's no trace of DDAS anywhere other than this error message. I also have no open connections to DDAS that might have somehow lingered through all of those restarts. 

If it matters: SQL Server Enterprise 2014 x64
If i am able to figure out correctly from below query 

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'DDAS_log', FILENAME = N'G:\DDAS_log.ldf')


I believe tempdb to be [DDAS] here..

Also, in the CREATE DATABASE Query

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DDAS')
BEGIN
    CREATE DATABASE [DDAS]
    CONTAINMENT = NONE
    ON  PRIMARY 
        ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf' , SIZE = 1024000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024000KB  ) 
    LOG ON      
        ( NAME = N'DDAS', FILENAME = N'G:\DDAS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    END
GO


Above the logical file name of log file seems wrong, instead of LOG ON
            ( NAME = N'DDAS'....) it should have been NAME = N'DDAS_log'..

For more info please refer CREATE DATABASE