question

SahaSaha-5270 avatar image
0 Votes"
SahaSaha-5270 asked Cathyji-msft edited

How to change the data file type of a database

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?
84587-datfilerename.jpg


sql-server-general
datfilerename.jpg (14.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @SahaSaha-5270,

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.
84658-screenshot-2021-04-06-103513.jpg

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

84793-screenshot-2021-04-06-101452.jpg

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

84675-screenshot-2021-04-06-101913.jpg

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]: /answers/storage/attachments/84628-screenshot-2021-04-06-101452.jpg



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.