This is one
of the common activities that DBA come across.Many DBAs find this topic
difficult and hence I thought of writing an article with relevant screenshots
for each step.I will discuss moving of both data and log files from one
location to another.
Let us
discuss each one of those in detail. . All the data and log files of both System
and User databases in this instance are on C drive and which leads to performance
degradation. I will move the files to D drive
Moving Data and Log files of User databases
To move data
and log files of user databases, the most simplest and fast way is to detach
the files and then re-attach them
Let us do
the same for user database 'Example'.
Step1:
Check the
current location of files.
sp_helpdb'example'
Data and log
files are at the location 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\Data' as shown in Fig 1.
Fig 1
I will move
the data files to the location 'D:\MSSQL\DATA' and log files to 'D:\MSSQL\LOG'.
Note:It is a best practice to place the
data and log files in Non- OS Drives i.e. on SAN Drives. By placing both data
and log files of a database in different drives increases the performance.
Dedicate a separate drive for Tempdb Data and Log files. Since, I do not have
more than 2 partitions in my PC; I am moving the files from C to D drive in
this exercise.
Step2:
Detach the
database using the following command.
use master
go
sp_detach_db 'Example'
go
Once you
have detached the database, database won't be listed under the databases as
shown in fig2.
Fig 2
Step3:
Move the
data files from 'C: \Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data' to
'D:\MSSQL\DATA' and log files to 'D:\MSSQL\LOG'.
Step4:
Re-attach
the database by listing all the files and its physical locations using the
following command.
use master
go
sp_attach_db 'Example',
'D:\MSSQL\DATA\Example.mdf',
'D:\MSSQL\DATA\Example_1.ndf',
'D:\MSSQL\LOG\Example_log.ldf'
go
After
re-attaching the database, it is once again listed under the databases as shown
in fig3.
Fig 3
Step5:
Re-check the
file location using
sp_helpdb'example'
Fig 4
This
completes moving of data and log files for user databases.
Moving Data and Log files of System databases:
How to move Tempdb files
How to move Model and MSDB files
Moving Data and Log files of System databases:
Unlike user databases system
databases cannot be detached normally. The following error appearswhen we try
detaching the system databases.
Fig 5
We need to
start SQL server in single user mode and with Trace flag 3608.Let me start with
tempdb which is very simple and move on to master.
How to move Tempdb files
Step1:
Check the
current file location
sp_helpdb'tempdb'
Fig 6
Step 2:
Since tempdb
is recreated every time the services are recycled, it’s not required to move
the files physically. We have to alter the file locationfor data and log files using
the following commandand then recycling the services will change the file
locations.
ALTER DATABASE TEMPDB MODIFY FILE(NAME=TEMPDEV,FILENAME='D:\MSSQL\DATA\TEMPDB.MDF')
GO
ALTER DATABASE TEMPDB MODIFY FILE(NAME=TEMPLOG,FILENAME='D:\MSSQL\LOG\TEMPLOG.LDF')
GO
Fig 7
Step 3:
Recycle the services and Re-check the file
location using
sp_helpdb 'tempdb'
Fig 8
Note1: After altering the file locations,
when the sql services are recycled for next time,tempdb files will be created
in the new locations. If it’s critical that you can't re-cycle the services,
you can recycle the service in off-peak hours. But, till that time, files at
the old location will be in use.
Note2: Once the services are recycled, files
will be created at the new location i.e. D:\MSSQL\DATA and D:\MSSQL\LOG folders
in this case. But, the files in old location are not deleted though they are
not in use. You can go ahead and delete them to save drive space.
How to move Model and MSDB files
Step 1:
Check the
current location of files.
sp_helpdb 'model'
go
sp_helpdb 'msdb'
Fig 9
Step 2:
Start sql
server in single user mode. This can be done in two ways.
Method 1: Go to Start --> All Programs --> Microsoft SQl Server 2005 --> Configuration Tools --> SQL Server Configuration Manager--> SQL Server 2005 Services.
On the right
pane, select the properties of SQL Server(MSSQLServer) ( orsql
server(Instancename)) and go to advanced tab and to the start up parameters as
shown in Figure 10.
Fig 10
And add the flags c, m and trace flag 3608 by typing –c-m-T3608 at the end as shown in figure 11 and then click on apply and restart the sql service to start the server in single user mode.
Fig 11
Method 2: Go to Start --> Run --> Type
cmd.
In the
command prompt type:
Net Stop MSSQLSERVER - for default instance or
Net stop MSSQL$Instancename - for other instances.
It will ask
for a confirmation to stop the Agent service, Type Y.Both SQL Server and Agent
services are stopped as shown in figure 12.
Fig 12
Now start
the sql server in single user mode using the switches /c /m and the Trace flag
3608
Type Net Start mssqlserver /c /m /T3608 (Type as it is as this is case sensitive). Now sql server server starts in single user mode.
Fig 13
Step 3:
Detach the
databases msdb and model.
SP_DETACH_DB 'MSDB'
GO
SP_DETACH_DB 'MODEL'
GO
Note 1: While connecting to the server after
it is started in single user mode, you may get the error as shown in fig 14.
Fig 14
By default
the object explorer is open and it is considered as one connection so if you
click new query it is considered as second connection and hence you get the
error.
Inorder to
avoid the error you need to click the disconnect button in the object explorer
pane and then close the object explorer window.Now you could see this window as
“No Server Connection”, as shown in Fig 15.
Fig 15
Now open a
new query window and connect to the server and execute the detach commands.
Note 2: Model and msdb are not listed under
the database list once detached.
Step 4:
Move the
data files from 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data' to
'D:\MSSQL\DATA'and log files to 'D:\MSSQL\LOG'.
Step 5:
Attach the
files and check the locations:
Fig 16
sp_helpdb 'msdb'
go
sp_helpdb'model'
Fig 17
Note:
If you use the above procedure, you are trying to detach the msdb
database while you detach the model database. When you do this, you must
reattach the model database first, and then reattach the msdb database. If you
reattach the msdb database first, you receive the following error message when
you try to reattach the model database:
Msg
0, Level 11, State 0, Line 0 A severe error occurred on the current command.
The results, if any, should be discarded.
In this
case, you must detach the msdb database, reattach the model database, and then
reattach the msdb database.
Step 6:
Now stop the server and restart it in normal
mode without any switches as shown in fig 18.
Fig 18
This
completes the movement data and log files of MSDB and Model databases.
Thus, the
article covers moving of both system and user databases in SQL Server 2005 and
SQL Server 2008. I will explain moving
the database files of Master and Resource databases in Part 2 of this article.
