CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Cannot create file ‘C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\DATA\<file_name>’ because it already exists. Change the file path or the file name, and retry the operation. (Microsoft SQL Server, Error: 1802)”</file_name>
You may experience this error when creating a database.
Consider the following scenario:
You have a database named “xxx”. By default, if you don’t specifically specify it, the data file name of this database is “xxx.mdf” and the name of the log file is “xxx_log.ldf”.
You want to create one more from the database named “xxx”, but you want to keep the one already created.
For this, you are changing the name of the old “xxx” database to “xxx_old”. But in this case, only the logical name of the database has changed. So the file names remain the same.
The name of the database that you are trying to create is “xxx”, so the file names are identical to the file names of the previously created “xxx” database, so you get errors.
If you are trying to create your database using SQL Server Management Studio, you can resolve this issue by specifying the file names yourself as you see below.
If you are creating your database with the CREATE DATABASE command, you can specify the file name as you see in the sample script below.
CREATE DATABASE [xxx] ON PRIMARY
(NAME = N'xxx_new', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\DATA\xxx_new.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
( NAME = N'xxx_log_new', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\DATA\xxx_log_new.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)