0

I uninstalled SQL Server and then reinstalled SQL Server on my laptop and now when I try to create an old database from my first installation called 'SSIS' and I get the following error:

Msg 5170, Level 16, State 4, Line 1 Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SSIS.mdf' because it already exists. Change the file path or the file name, and retry the operation. Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

When I try to access the "DATA" folder I can't.

I can create other databases but not the one named "SSIS" I've searched online and others have had similar errors but their fixes did NOT work for my laptop.

New contributor
joshd2021 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
4
  • "When I try to access the "DATA" folder I can't." - What does this mean?...do you get an error and what does it say?...surely you should have access to the folder (if it exists) on your own laptop.
    – J.D.
    Jun 20 at 17:10
  • Probably a permission issue where mssql is the owner and you need to grant yourself permission to it first. Jun 20 at 17:16
  • So when I try to double click the "DATA" folder from this file path from the error "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SSIS.mdf" to delete the "SSIS.mdf" file nothing happens.
    – joshd2021
    Jun 20 at 17:16
  • @TiborKaraszi how can I check and change my permission if that's the issue?
    – joshd2021
    Jun 20 at 17:18

2 Answers 2

1

It seems that the database files for the SSIS database have remained in place after the uninstall. I believe user database files are not deleted by the uninstall process (presumably by design) and as others have said you need to delete the file, or create the new database in another location

You should be able to delete the file from an administrative Powershell prompt using

Remove-Item "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SSIS.mdf" -Force

You will possibly find the same issue with the log file so may need to run

Remove-Item "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\SSIS_log.ldf" -Force

(assuming the path / filename is correct)

15
  • I tried running "Remove-Item "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SSIS.mdf" -Force" in the Administrative Powershell prompt and an error was returned which I will post in another comment due to comment length restriction.
    – joshd2021
    Jun 20 at 18:22
  • 1
    Correct - the admin prompt will let you remove files regardless of the permissions. As to why the uninstall didn't delete them. I believe when you uninstall a SQL Server instance, it keeps the database files for the user databases presumably so you can't say "SQL Server trashed my data" please mark as the answer if this helped
    – SEarle1986
    Jun 20 at 19:17
  • 1
    I would recommend "Fundamentals of Database Administration" course by Brent Ozar on Youtube. A lot of learning can just be trial, error and Google though :)
    – SEarle1986
    Jun 20 at 19:20
  • 1
    @joshd2021 For future reference, status updates can be added to your original post instead of comments, to avoid the character restrictions. In fact, this is probably the recommended way to do so.
    – J.D.
    Jun 20 at 22:05
  • 1
    @J.D. Thanks for letting me know, I'll make sure to do that in the future!
    – joshd2021
    Jun 21 at 23:05
0

A SQL Server database consists of data files .mdf and a log file .ldf

The paths to these files are specified when creating a database:

enter image description here

The error you get means that a data file with that name already exists.

If this file is not used by any database, then you can just remove it.

--

If used - please specify other paths or names for the database files when creating a database.

--

That the following directory C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA is created by SQL Server and users do not have access to it. To get access to this directory run Explorer as administrator.

1
  • The Powershell way solved the problem but even running file explorer as an Admin doesn't let me access that "DATA" folder.
    – joshd2021
    yesterday

Your Answer

joshd2021 is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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