It’s Very Simple 10 steps to do this.
In this example System database files existing in C: Drive and I will move it in D: Drive.
Step1- Check the D drive is access. Right click on D drive and go to Properties, go to security tab and find the Administrator “group or username” is there on not.
Step2- Next Step to create a folder in D drive. And the folder name should be as SQL server “Instance ID” name, which is “MSSQL10.MSSQLSERVER”. We can get this name from existing SQL server path for example see following path:-
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
We can say this folder is the Data container for all System Database
files.
Now we have to check the folder
permission of the folder and if anything is missing so we will give that
permission to newly created folder “MSSQL10.MSSQLSERVER”.
Step 3-
Open the both old folder and newly create folder properties and click on security
tab and match the old folder and newly create folder “group name” and “permission” and click on ADD button than search the “group name” and ADD it in new folder.
Step4-
Now we will move the Model and MSDB and
TEMPDB database.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = modeldev, FILENAME = 'D:\MSSQL10.MSSQLSERVER\model.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = modellog, FILENAME = 'D:\MSSQL10.MSSQLSERVER\modellog.ldf');
GO
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = msdbdev, FILENAME = 'D:\MSSQL10.MSSQLSERVER\msdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = msdblog, FILENAME = 'D:\MSSQL10.MSSQLSERVER\msdblog.ldf');
GO
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\MSSQL10.MSSQLSERVER\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\MSSQL10.MSSQLSERVER\templog.ldf');
GO
Step 5-
Stop the SQL server instance and
COPY the Model and MSDB database file form C: Drive and PASTE it into D Drive- D:\MSSQL10.MSSQLSERVER . Now we can START the SQL server instance.
Note- No Need to move TEMPDB files. Once the instance will START it will
create automatic new TEMPDB log files.
Step 6-
Now we have to move Master database. Go to Configuration
manager, Right click on SQL server Instance and Properties, Click on Advanced
tab. Here we will get the “Startup
Parameters” value.
“Startup Parameters” value:-
-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
SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
In this value we need to change
the Path of each log file. Our current log file path is- D:\ MSSQL10.MSSQLSERVER
New “Startup Parameters” value:-
-dD:\ MSSQL10.MSSQLSERVER\master.mdf;-eC:\Program
Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\ MSSQL10.MSSQLSERVER \mastlog.ldf
Step 7-
Now we have to STOP the SQL server instance. Copy the Master database log files and Paste
it in D Drive D:\ MSSQL10.MSSQLSERVER.
Step 8-
Verify all the file has been moved in New Location,
Step 9- Delete
the ALL the System Database files from C drive.
Step 10-
Now we must have to START the SQL server instance.
AND ROCK and ROLL
