How to Change Database Default Locations in SQL Server?
Sometimes, we need to change default database File locations to some other path in SQL Server. If you have configured SQL Server default database file location as drive C then all SQL Server databases will be created in the path set as database default locations if you have not mentioned their specific drives during database creation. You might need to change this path as per your environment standard. Here, I am going to show you how to view or change database default location in SQL Server.
One thing we should always keep in mind that if we change database default path in SQL Server then it will not change or move the database file locations for your existing databases. If you want to move all existing databases or you want to change database files path to the new location then you need to manually do it for each database.
Once you will change default database file locations then all databases you will create on that SQL Server Instance will be created on new location. Suppose your existing SQL Server default database location is set in C drive and now you have decided to change it to D drive for data files and F drive for Log files then all databases you will create post this change will be created in these both drives. If you will mention any other specific drive during database creation then SQL Server uses that drive and not these database default locations. The SQL Server data file will be created in D drive and log file will be created in F drive but all the existing databases that were created earlier in C drive will not move to new SQL server database file location. If you want to move these databases or want to change database files location then you need to manually do it either by detach-attach method or by ALTER statement.
Change Database Default Locations in SQL Server
It’s very simple method to change default log file location or default data file location in SQL Server. Let’s start to change default database path in SQL Server.
- Launch SQL Server Management Studio and connect to your SQL Server Instance for which you must change default log file location or default data file location in SQL Server.
- Now right click at SQL Server Instance node and choose Properties option.
- You will get server properties window on your screen. Click at “Database Settings” from left side pane. You will get all server level details for SQL Server databases in right side pane. Look at the database default locations option given bottom of the right-side pane. Here you can see separate default location for data files, Log file and backup files.
- You can see database default locations for each file are showing in C drive. It means every database that will be created without mentioning their data or log file location will go to above given SQL Server database default locations.
- To change database default path, you need to browse current database default locations for each file to new the new location. Click at three dots showing next to data file or log file locations and then select the new path.
- Repeat same exercise for each file. To change default data file location, click at three dots next to data path and select new path here. Similarly, click at three dots next to log file location to change default log file location. If you want to change default backup file location then do the same for backup location as well.
- Click ak Ok button once you made changes for desired database file locations.
- Last step is to restart SQL Server services to apply this change and bring it into effect.
Once you will restart SQL Server services, your SQL Server default database location will be changed to the new path. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
- Get Total Number of Virtual Log Files in SQL Server Transaction Log File
- SQL Server Error 1105: Could Not Allocate Space for Object in Database because the filegroup is full
- How to Change SQL Server Instance Name?
- Why Should You Always Turn Off Database Auto Shrink Property?
Latest posts by Manvendra Deo Singh (see all)
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018
- How to Add Data File to Availability Database? - July 11, 2018