- 1 year ago
Script to change the SQL Default database location in windows
Comment
1 / 1
To change the default database location in SQL Server on Windows, you can use the following script:
sqlUSE master;
GO
-- Update the default database location
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'default data location', 'C:\NewDataLocation';
GO
RECONFIGURE;
GO
-- Move existing system databases to the new location
ALTER DATABASE master MODIFY FILE (NAME = 'master', FILENAME = 'C:\NewDataLocation\master.mdf');
ALTER DATABASE mastlog MODIFY FILE (NAME = 'mastlog', FILENAME = 'C:\NewDataLocation\mastlog.ldf');
ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = 'C:\NewDataLocation\model.mdf');
ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = 'C:\NewDataLocation\modellog.ldf');
ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBData', FILENAME = 'C:\NewDataLocation\msdbdata.mdf');
ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBLog', FILENAME = 'C:\NewDataLocation\msdblog.ldf');
GO
-- Restart the SQL Server service for changes to take effect
-- Note: This step requires administrative privileges
-- You can manually restart the SQL Server service or use the appropriate command for your environment
In this script:
- The
sp_configure
stored procedure is used to enable the display of advanced configuration options. - The
RECONFIGURE
statement is used to update the configuration settings. - The
sp_configure
stored procedure is used again to set the default data location to the new path. - The
RECONFIGURE
statement is called to apply the new configuration. - The
ALTER DATABASE
statements are used to modify the file paths of the system databases (master, model, msdb) to the new location.
After running this script, you need to restart the SQL Server service for the changes to take effect. Make sure you have administrative privileges to restart the service.
It's important to note that changing the default database location should be done with caution and only after proper planning and understanding of the potential impact. Additionally, ensure that the new location has appropriate disk space and access permissions for the SQL Server service account.