locked
Move SQL server 2017 database to another drive RRS feed

  • Question

  • hello

    I'm looking to move an SQL 2017 database to another drive. A software company has installed a package that required SQL, we setup the SQL server and then the software company installed the package creating two new databases rather than use the two we prepared for them.

    The issue is that they created both databases on the same drive rather than separate the databases on the two drives we had prepared. Looking at moving the database it is recommended to use  ALTER DATABASE and herein lies my issue.

    They created a user database files for each database with the same name, database1.mdf / database1.ndf and  database2.mdf / database2.ndf,

    How do i use MODIFY FILE when both the mdf and ndf files have the same name 'database2', if we were creating user files we would normally differentiate the file names such as database2.mdf and database2_user.ndf then use

    ALTER DATABASE database2   
        MODIFY FILE ( NAME = database2   
                      FILENAME = 'G:\MSSQL\Data\database2.mdf'); 

    ALTER DATABASE database2   
        MODIFY FILE ( NAME = database2_user   
                      FILENAME = 'G:\MSSQL\Data\database2_user.ndf'); 

    how do you move the database files when both have the same filename?
    Friday, May 31, 2019 10:14 AM

Answers

  • The file extension is considered part of the filename for uniqueness so you can have files in the same folder that differ only by extension (e.g. database2.mdf and databse2.ndf). Of course, you can change the file name too if that is your preference.

    See https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-2017 for the procedure for planned user database file relocation.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, May 31, 2019 10:24 AM

All replies

  • The file extension is considered part of the filename for uniqueness so you can have files in the same folder that differ only by extension (e.g. database2.mdf and databse2.ndf). Of course, you can change the file name too if that is your preference.

    See https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-2017 for the procedure for planned user database file relocation.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, May 31, 2019 10:24 AM
  • thank you
    Friday, May 31, 2019 10:28 AM