How to change the data file type of a database

Question

During the process of rename the datafile file one of the datafile file type is 'File' instead of 'SQL Server Database Primary Data File'. Is there anyway we can convert the 'data file type to SQL Server Database Primary Data File?

Answer 1

Hi @Avyayah ,

Did you change the File Extension for a Data File? We recommends you use MDF (Primary Data File), NDF (Secondary Data File - this is optional) for SQL server data files.

Below is a test in my environment, we need to change the File Extension to MDF for the SQL Data File. Then the data file type is changed to 'SQL Server Database Primary Data File'.

1. Set database offline, then change the File Extension for database data file. ALTER DATABASE [test2] SET OFFLINE
   GO

   ALTER DATABASE [test2]   
   MODIFY FILE (name = 'test2', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\test2.data')  
   GO  
   

2. Change the File Extension for database data file by renaming data file

3 Set database online.

ALTER DATABASE [test2] SET ONLINE  
GO  

When you want to change the data file type to 'SQL Server Database Primary Data File'. Please follow below steps.

1. Set database offline, then change the File Extension for database data file. ALTER DATABASE [test2] SET OFFLINE
   GO

   ALTER DATABASE [test2]   
   MODIFY FILE (name = 'test2', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\test2.mdf')  
   GO  
   

2. Change the File Extension for database data file by renaming data file

3 Set database online.

ALTER DATABASE [test2] SET ONLINE  
GO  

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you. 2: /api/attachments/84628-screenshot-2021-04-06-101452.jpg?platform=QnA