Moving SQL Database to a different drive

This situation usually arises when we might have to move SQL DB files to other drives at times due to space crunch. We can follow this activity to move these types of databases.

• Configuration database
• Central Administration content database
• Content databases
• Service application databases

This activity was carried out in MOSS 2007 with SQL 2005

P.S.

i) Make sure that a backup of the database is already taken before you start with this activity.

ii) Also check the DB owners remain the same before and after this activity.

iii) You might have to do a full crawl after completion of activity if results are not showing up in search

1) Quiesce the Farm

This is done to ensure that no new user sessions are initiated. Browse to Central Administration -> Operations -> Global Configuration -> Quiesce farm. Click "Start Quiescing".

2) Stop the below SharePoint Services

Type services.msc in Run.

1. Office SharePoint Server Search (alternatively net stop OSearch from command prompt)

2. Windows SharePoint Services Search (alternatively net stop SPSearch from command prompt)

3. Windows SharePoint Services Timer (alternatively net stop SPTimerV3 from command prompt)

4. Windows SharePoint Services Administration service (alternatively net stop SPAdmin from command prompt)

 5. Windows SharePoint Services Tracing service (alternatively net stop SPTrace from command prompt)

6. Windows SharePoint Services VSS Writer service (alternatively net stop SPWriter from command prompt)

 

3) Detach Database

Login to SQL Server. Run the below query:

use master

go

exec sp_detach_db @dbname = N ‘DBName', @keepfulltextindexfile='true';

go

Where DBName = content db /search ssp db you want to move

4) Move files to target location

Move the data and log files from the current drive location (F:\) to the target drive location (D:\).

Include the .mdf files and .ldf files

5) Attach Database

Login to SQL Server. Run the below query:

use master

go

exec sp_attach_db @dbName = N'DBName',

@filename1 = N'LocalPathToFile\DBName.mdf',

 @filename2 =N 'LocalPathToFile\DBName.ldf'

go

Where DBName = content db /search ssp db you moved

                    LocalPathToFile = file location

6) Start the below SharePoint Services

Type services.msc in Run

1. Office SharePoint Server Search (alternatively net start OSearch from command prompt)

2. Windows SharePoint Services Search (alternatively net start SPSearch from command prompt)

3. Windows SharePoint Services Timer (alternatively net start SPTimerV3 from command prompt)

4. Windows SharePoint Services Administration service (alternatively net start SPAdmin from command prompt) 

5. Windows SharePoint Services Tracing service (alternatively net start SPTrace from command prompt)

6. Windows SharePoint Services VSS Writer service (alternatively net start SPWriter from command prompt)

7) UnQuiesce the farm

Browse to Central Administration -> Operations -> Global Configuration -> Quiesce farm. Click "Reset Farm".

8) Try testing your application now.(access the sites, test search and ensure search settings are intact)