HOWTO: Moving WSS3 Database to a different drive

HOWTO: Moving WSS3 Database to a different drive (out from c:\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data)
rakb200810080402

Why move the default location of the SQL Content db and the SQL Search db to a different drive?
The default SQL db location for Sharepoint 3.0 is in c:\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data which is great for a site that is not heavily used like a home server or a small office intranet.  But when Sharepoint is being deployed to an intranet that is potentially large ie. SAS, the Content and Search DB needs to be relocated away from c:\drive (system drive).  This files can grow so fast that out of the blue, the system drive will run out of space and crash — BSOD. There is no size limit for the SQL Embeded version of Sharepoint DB, so the physical limit will be the drive space and that is the reason why those 2 growing files needs to be relocated on a different drive. If we relocate those 2 files to a 2nd physical drive (not a partition), the site will perform faster and easier to recover during a disaster.

An Example of a good Sharepoint Server hardware configuration:
Memory=4GB
Drive0=c:\drive
Drive1=c:\drive mirror
Drive2=d:\drive
Drive3=d:drive mirror
1. Install SQL Server Native Client Tool, Download Here
2. Install SQL Command Line Tool, Download Here
3. Create a wss folder in c:\drive to hold the movesharepoint.bat and sharepoint.sql
4. Create a sharepointdb folder in d:\drive to hold the SQL Server .mdf and .ldf files
5. Create a batch file and call it movesharepoint.bat (Courtesy from Ken Warren)
NOTE: The highlighted items correspond to the changes I made on the script to match the drive letter of my serverG:=my c:drive
W:=my 2nd SCSI drive where I wanted to.mdf/.ldf to reside.
PE1550e=my Server Name
————————————-
net stop mssql$microsoft##ssee
copy G:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\WSS_Content*.* W:\SharePointDB\*.*
copy G:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\WSS_Search*.* W:\SharePointDB\*.*
net stop SPSearch
net start mssql$microsoft##ssee
“G:\Program Files\Microsoft SQL Server\90\Tools\binn\sqlcmd” -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i G:\wss\sharepoint.sql
net start SPSearch
————————————-
6. Creat a sql script and name it sharepoint.sql
————————————-
EXEC sp_detach_db @dbname = ‘WSS_Content’
go
EXEC sp_detach_db @dbname = ‘WSS_Search_PE1550E’
go
EXEC sp_attach_db @dbname = ‘WSS_Content’, @filename1 = ‘W:\SharePointDB\WSS_Content.mdf’, @filename2 = ‘W:\SharePointDB\wss_content_log.ldf’
go
EXEC sp_attach_db @dbname = ‘WSS_Search_PE1550E’, @filename1 = ‘W:\SharePointDB\WSS_Search_PE1550E.mdf’, @filename2 = ‘W:\SharePointDB\WSS_Search_PE1550E_log.ldf’
go
————————————-

7. Open Sharepoint Central Administration –>Application Management Tab–>Click Content Databases
8. Click on the link for WSS_Content
9. In the Database dropdown, select Offline, Click OK
10. Minimize this Sharepoint Central Administration Page, you will need it later to place the db back Online.
11. Verify if you created the folder d:\sharepointdb mentioned in Step 4 above.
12. Go to the script folder c:\wss
13. Open command prompt on this folder
14. At the command prompt, type movesharepoint.bat
15. After the script is complete, Open Sharepoint Central Administration –>Application Management Tab–>Click Content Databases
16. Click on the link for WSS_Content
17. In the Database dropdown, select Ready, Click OK
18. Go to d:\sharepoint folder and try to rename WSS_Content.mdf, if you get an error, the move was successful
19. Go to d:\sharepoint folder and try to rename WSS_Search_Servername.mdf, if you get an error, the move was successful
20. Upload files to your sharepoint site and monitor the growth size of the files WSS_Content.mdf  WSS_Search_Servername.mdf
Here’s the command prompt snapshot of a succesful movesharepoint.bat

G:\wss>movesharepoint
G:\wss>net stop mssql$microsoft##ssee
The Windows Internal Database (MICROSOFT##SSEE) service is stopping.
The Windows Internal Database (MICROSOFT##SSEE) service was stopped successfully
.

G:\wss>copy G:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\WSS_Content*.* W:\Share
PointDB\*.*
G:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\WSS_Content.mdf
G:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\WSS_Content_log.LDF
        2 file(s) copied.
G:\wss>copy G:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\WSS_Search*.* W:\ShareP
ointDB\*.*
G:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\WSS_Search_PE1550E.mdf
G:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\WSS_Search_PE1550E_log.LDF
        2 file(s) copied.
G:\wss>net stop SPSearch
The Windows SharePoint Services Search service is stopping…….
The Windows SharePoint Services Search service was stopped successfully.

G:\wss>net start mssql$microsoft##ssee
The Windows Internal Database (MICROSOFT##SSEE) service is starting.
The Windows Internal Database (MICROSOFT##SSEE) service was started successfully
.

G:\wss>”G:\Program Files\Microsoft SQL Server\90\Tools\binn\sqlcmd” -S \\.\pipe\
mssql$microsoft##ssee\sql\query -E -i G:\wss\sharepoint.sql
G:\wss>net start SPSearch
The Windows SharePoint Services Search service is starting…
The Windows SharePoint Services Search service was started successfully.

G:\wss>