Home » Topic » How to change the database location in SQL Server?

How to change the database location in SQL Server?

Stephen West ~ Modified: February 2nd, 2016 ~ ~ 1 Minute Reading

Home › Forums › How to change the database location in SQL Server?

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • February 2, 2016 at 1:04 pm #1071 Score: 0
    Stephen West
    Moderator
    4 pts

    I want to change default database location? Can anybody explain me how to change the default SQL Server database location via SQL Server Management Studio or Transact-SQL

    February 4, 2016 at 1:18 pm #1096 Score: 0
    Andrew Jackson
    Moderator
    1 pt

    Open SQL Server Management Studio and right click on the SQL Server Instance. From popup menu, click on properties
    Click on Properties
    From server properties dialog box, select a database setting, then change the default database location and click ok.
    Properties Dialog Box
    You need to restart the SQL Server Services to come change into effect

    February 15, 2016 at 1:42 pm #1106 Score: 0
    Lincoln Burrows
    Moderator
    16 pts

    This won’t create the new path for you, this will change the default database to already exists path only

    February 16, 2016 at 1:05 pm #1107 Score: 0
    Stephen West
    Moderator
    4 pts

    Can you please tell me how to create a new path for existing database in SQL Server

    February 18, 2016 at 1:20 pm #1117 Score: 0
    Andrew Jackson
    Moderator
    1 pt

    To change the path of an existing database

    • Issue an ALTER DATABASE … MODIFY FILE command
    • Stop the SQL service
    • Move the database files to a new location
    • Restart the service
    February 22, 2016 at 1:19 pm #1149 Score: 0
    Lincoln Burrows
    Moderator
    16 pts

    You can also change the path of an existing database using transact-SQL

    • First, detach database:
      • USE master;
GO
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db @databasename = N'DatabaseName';
GO
    • Then copy the detach database file to a new location
    • At last attach the database:
      • USE master;
EXEC sp_attach_db @databasename = N'dbName', 
@filename1 = N'',  --path do .mdf
@filename2 = N'';  --path to .ldf
GO
    March 18, 2016 at 12:27 pm #1698 Score: 0
    Andrew Jackson
    Moderator
    1 pt

    You can try this also that changes the default database location.

    ALTER DATABASE my SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE my SET OFFLINE;

ALTER DATABASE my MODIFY FILE 
(
   Name = my_Data,
   Filename = 'D:\DATA\my.MDF'
);

ALTER DATABASE my MODIFY FILE 
(
   Name = my_Log, 
   Filename = 'D:\DATA\my_1.LDF'
);
  • Author
    Posts
Viewing 7 posts - 1 through 7 (of 7 total)
  • You must be logged in to reply to this topic.