Moving SQL System Databases from one drive to other drive .
Master
1) Open SQL Server Configuration manager from program files.
1) Open SQL Server Configuration manager from program files.
2)In SQL Services , right click on SQL Server(i.e.MSSQLSERVER or Named instance) and choose Properties
3) Go to Advanced TAB and Edit Startup Parameters values to point to planned location for the master database data and log files and then click on ok.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.
-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
LOG\ERRORLOG;-lC:\Program Files\Microsoft SQLerver\MSSQL10.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf
If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:
-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf
stop the instance by right click and choose stop and move the master.mdf and master.ldf to new location.and restart the instance.
Verify the file change for the master database by running the following query.
CopySELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.
-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
LOG\ERRORLOG;-lC:\Program Files\Microsoft SQLerver\MSSQL10.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf
If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:
-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf
stop the instance by right click and choose stop and move the master.mdf and master.ldf to new location.and restart the instance.
Verify the file change for the master database by running the following query.
CopySELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO
Model Database
1.Open SQL Server Configuration Manager
2.Stop the SQL Server Service and make sure SQL Server Agent is stopped as well
3.Open the properties of SQL Server, in Advance tab, add parameter -c;-m;-T3608 into the parameter values, and then click OK. That's makes SQL to be openned in single user mode.
4.Restart SQL Server Service and Open SQL Server Management Studio without connect to any server. And then click on New Query and run the query:
use master
go
sp_detach_db ‘model’
go
5.Move modeldata.mdf and modellog.ldf in drive C (usually in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) to another drive
6.Delete the added parameter and restart service of SQL Server and SQL Server Agent
7.Attach Model database using query:
use master
go
sp_attach_db ‘model’,’E\sqldata\modeldata.mdf’, ’F:\ sqldata \modellog.ldf’
go
1.Open SQL Server Configuration Manager
2.Stop the SQL Server Service and make sure SQL Server Agent is stopped as well
3.Open the properties of SQL Server, in Advance tab, add parameter -c;-m;-T3608 into the parameter values, and then click OK. That's makes SQL to be openned in single user mode.
4.Restart SQL Server Service and Open SQL Server Management Studio without connect to any server. And then click on New Query and run the query:
use master
go
sp_detach_db ‘model’
go
5.Move modeldata.mdf and modellog.ldf in drive C (usually in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) to another drive
6.Delete the added parameter and restart service of SQL Server and SQL Server Agent
7.Attach Model database using query:
use master
go
sp_attach_db ‘model’,’E\sqldata\modeldata.mdf’, ’F:\ sqldata \modellog.ldf’
go
Tempdb
ALTER DATABASE tempdb
MODIFY FILE ( NAME =’tempdev’,
FILENAME = ‘E:\Microsoft SQL Server\MSQL\Data\tempdev.mdf’)
ALTER DATABASE tempdb
MODIFY FILE ( NAME =’templog’,
FILENAME = ‘E:\Microsoft SQL Server\MSQL\Log\templog.ldf’)
MSDB Database
1.Open SQL Server Configuration Manager
2.Stop the SQL Server Service and make sure SQL Server Agent is stopped as well
3.Open the properties of SQL Server, in Advance tab, add parameter -c;-m;-T3608 into the parameter values, and then click OK. That's makes SQL to be openned in single user mode.
4.Restart SQL Server Service and Open SQL Server Management Studio without connect to any server. And then click on New Query and run the query:
use master
go
sp_detach_db ‘msdb’
go
5.Move msdbdata.mdf and msdblog.ldf in drive C (usually in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) to another drive
6.Delete the added parameter and restart service of SQL Server and SQL Server Agent
7.Attach MSDB database using query:
use master
go
sp_attach_db ‘msdb’,’D:\Database Files\msdbdata.mdf’, ’D:\ Database Files \msdblog.ldf’
go
