Create new DB with existing filepath

Question

I want to create new DB that use the filepath the same as used for another DB, is it possible?

I get the error:

Unable to open the physical file "E:\Test\SQL\Data\TestDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".

Msg 1802, Level 16, State 7, Line 14

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

This is the code:

CREATE DATABASE [TestDBNew]
 ON  PRIMARY 
( NAME = N'TestDB', FILENAME = N'E:\Test\SQL\Data\TestDB.mdf' , SIZE = 7680000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
 FILEGROUP [TestDBFilestream] CONTAINS FILESTREAM  DEFAULT
( NAME = N'TestDBFS_01', FILENAME = N'E:\Test\SQL\Data\TestDBFS_01' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'TestDB_log', FILENAME = N'E:\Test\SQL\Logs\TestDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

  FOR ATTACH
GO

Answer 1

SQL Server hold exclusive locks on all (database) file, you can not share file for other databases.

Answer 2

Hi @Maria Benich

The error message is already obvious. You cannot create a new DB with the same file path as an existing database.

Best regards,

Percy Tang

Answer 3

If there already is a file E:\Test\SQL\Data\TestDB.mdf, you cannot create a new database using that file, even less so if the existing file is attached to another SQL Server instance, which seems to be the case here.

Is there any particular reason you want to create the database with this very path that is already in use?