MOVING DATABASES IN SQL SERVER 2005 and SQL SERVER 2008

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:

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.