How to Change the Database Location in SQL Server?

Question

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

Edwin J. Hoffer · Accepted Answer

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

Mariya Beckham · Answer

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

David Ross · Answer

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

Kerry Wilson · Answer

To change the path of an existing databasen n Issue an ALTER DATABASE. MODIFY FILE command n Stop the SQL service n Move the database files to a new location n Restart the service n

Edwin J. Hoffer · Answer

You can also change the path of an existing database using transact-SQLn n First, detach database: n  USE master; nGOnALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; nGOnEXEC sp_detach_db @databasename = N' DatabaseName' nGOn  nn Then copy the detach database file to a new location n At last attach the database: n  USE master; nEXEC sp_attach_db @databasename = N

Mariya Beckham · Answer

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

ALTER DATABASE my SET SINGLE_USER WITH ROLLBACK IMMEDIATE; nnALTER DATABASE my SET OFFLINE; nnALTER DATABASE my MODIFY FILE n(n Name = my_Data, n Filename = ' D:\DATA\my. MDF' n); nnALTER DATABASE my MODIFY FILE n(n Name = my_Log, n Filename = ' D:\DATA\my_1. LDF' n);

n