Our internal SBS2003 box and one at a clients were starting to get a bit short of space on the C: drive. One of the culprits were the Sharepoint database files which were well over a gig on our system and .5 gig at our clients.

I read up various documents on the MS site and a few NG and blog posts before trying this and I have now successfully moved both sets of Sharepoint databases. I would strongly recommend you read the document at http://download.microsoft.com/download/1/1/6/11671e4d-fb21-489c-870f-db36fd21a7d2/SBS_MoveDataFolders.DOC before doing this.

Because I'm basically lazy by nature and prefer to do things the easy way and I really don't like typing in obscure OSQL commands I decided to write a batch file that would do it all for me.

Here it is:

-------------------------------- 

@echo off

rem Move CompanyWeb

rem Based on MS document and posts on newsgroups

rem YOU MUST CHANGE THESE SOURCE AND TARGET FOLDERS TO MATCH YOUR OWN

rem DON'T FORGET THE TRAILING "\" EITHER OTHERWISE NASTY THINGS WILL HAPPEN.....

set source=C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\
set target=S:\CompanyWeb\

echo .
echo Are you connected via Remote Web Workplace?
echo .
echo If so, hit CTRL-C to terminate this batch file and connect via RDP
echo or from the console and run this batch file again
echo .
echo This batch file is about to stop the IIS services on this server.
echo.
pause

@echo on

iisreset /stop
Net stop sptimer

rem The MSSQL$SHAREPOINT service must be running, but stop and start it beforehand otherwise sometimes the
rem detach of the STS_ServerName_1 database fails with it being flagged as in use

net stop MSSQL$SHAREPOINT
net start MSSQL$SHAREPOINT

Osql -E -S %computername%\SharePoint -Q "sp_detach_db 'STS_Config'"
Osql -E -S %computername%\SharePoint -Q "sp_detach_db 'STS_%computername%_1'"

xcopy "%source%STS_%computername%_1.mdf" "%target%STS_%computername%_1.mdf"
xcopy "%source%STS_%computername%_1_Log.ldf" "%target%STS_%computername%_1_Log.ldf"
xcopy "%source%STS_Config.mdf" "%target%STS_Config.mdf"
xcopy "%source%STS_Config_Log.ldf" "%target%STS_Config_Log.ldf"

Osql -E -S %computername%\SharePoint -Q "sp_attach_db 'STS_Config', '%target%STS_Config.mdf', '%target%STS_Config_Log.ldf'"

Osql -E -S %computername%\SharePoint -Q "sp_attach_db 'STS_%computername%_1', '%target%STS_%computername%_1.mdf',

'%target%STS_%computername%_1_Log.ldf'"

iisreset /start
Net start sptimer

rem Just do another iisreset for good measure :-)

iisreset

-----------------------------------

Just a few things to note:

• Don't try running this in a Remote Web Workplace session. It closes IIS down which tends to make RWW somewhat non-functional.
• You must change the Source and Target folders each time you run this script. You will need to create the target folder before you start
• Don't forget the trailing "\" on the Source and Target folders otherwise nasty and unfortunate things might happen.
• Each of the XCOPYs will ask whether the target is a File or a Directory. Obviously it's a File
• The MSSQL$SHAREPOINT service is bounced before starting because I found that my client's machine was saying that the database was still in use when it wasn't. Bouncing the service seemed to clear it.
• Obviously this script comes "as is". If you use it it's up to you to check that it does what you want and in a way that you want it to. If you use it let me know and if you find any problems with it then please do let me know so I can correct them. I have used the script on two servers and am pretty confident it does what it should, but there are no guarantees. (added 13/08/06) I've used it on another client's server this afternoon and it worked as expected.
• I would strongly suggest you run this from a command prompt not just be double-clicking the batch file in Explorer.

Hope this is useful for someone.