SQL Server: attach database without a log file-CodePudding

I have uninstalled my SQL Server 2019 and installed SQL Server 2022 Express. All my databases are under:

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA

My new location is:

C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA

Attaching a database directly from the old location results in:

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\DBName.mdf". Operating system error 5: "5(Access is denied.)".

I then copied the .mdf file to the new location. But trying to attach it from the new location, I get a similar error, but for the log file:

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\DBName_log.ldf". Operating system error 5: "5(Access is denied.)".

I can't copy the log file into the new location because I don't have enough space on my HD.

Any idea?

Thanks

CodePudding user response：

Operating system error 5: "5(Access is denied.)".

SQL Server locks down the data folder with NTFS ACLs. So you can fix this by taking ownership of the folder and files and granting full control to the other SQL Server instance.

SQL Server disables permissions inheritance on the files, so you need to grant full control to the other SQL instance to each file. And if you're connected using Windows Integrated Auth, you'll also need to grant yourself full control over the files.

In my test, after granting full control over the mdf and ldf to 'NT Service\MSSQLSERVER' and to my windows user, I was able to run

CREATE DATABASE [Foo]
 ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\Foo.mdf'  ),
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\Foo_log.ldf' )
for attach

from the default instance. You can also move (not copy) the files without needing additional space.