SharePoint: WSS Installation On A DC – Move Database
In Windows SharePoint Services v3, the databases ( there are quite a few databases) cannot be installed in a drive other then %systemdrive%. Yeah! It is called the Windows Internal Database. To move this database, you need to do some cool acrobats on the server! If you are comfortable with the thought of moving the database using GUI, head over to this site. Wait! I will help you do this with getting into the SharePoint Central Administration. How cool is that? Of course my steps are based on the fantastic post by Ian Morrish.
I am going to draw some excerpts from Ian’s post so that you understand what needs to be done and what I have done.
If you don’t have another SQL Server 2005 product installed on the WSS server,
Download SQL Server 2005 Command Line Query Utility (x86)
Download SQL Server Native Client (x86)
Source page for other things SQL
I have placed the above files in C:WSSV3 (will be standard folder for everything WSSV3)
Ok, now lets get my script out to install
c:wssv3sqlncli.msi /qn (quite and no notifcation)
c:wssv3sqlserver2005_sqlcmd.msi /qn
After installation, you will find the utility installed in the following location:
C:Program FilesMicrosoft SQL Server90Toolsbinn
The Windows Internal Database is located in %windir%sysmsisseeMSSQL.2005MSSQLData
Now another script
net stop MSSQL$microsoft##ssee
mkdir c:SharePointv3Database
copy %windir%sysmsisseeMSSQL.2005MSSQLDataWSS_Content*.* c:SharePointv3Database*.*
net start MSSQL$microsoft##ssee
sqlcmd -S .pipemssql$microsoft##sseesqlquery -E -i c:s2.sql
pause
del %windir%sysmsisseeMSSQL.2005MSSQLDataWSS_Content*.*
Lets understand this script:
WSS 3.0 ships with a new Windows Internal Database based on SQL Server 2005.
You will see it as a running service called MSSQL$MICROSOFT##SSEE. The first line stops this.
Then copy database which incidentally is WSS_Content to C:SharePointV3Database.
Start the service.
Now this is interesting. I need to detach the database and then reattach. I have done this as above. s2.sql contains
EXEC sp_detach_db @dbname = ‘WSS_Content’
go
EXEC sp_attach_db @dbname = ‘WSS_Content’, @filename1 = ‘C:SharePointv3DatabaseWSS_Content.mdf’, @filename2 = ‘C:SharePointv3databasewss_content_log.ldf’
go
Now delete the database.
This solution works. It works. It works!!!
Next UP: Using Configuration Wizard without the wizard!
March 30th, 2007 at 8:43 pm
Hi Alps,
This stuff is fantastic and very informative. I have been working with sharepoint since version 2003 now we are also using wss v3 it great and I am picking up some great tips from you.
Can I just take the time to say keep up the good work. Its people like you that help the internet and sharepoint community grow.
Cheers again
Steven
March 30th, 2007 at 9:45 pm
Hi Steven,
Thank you for your kind words. I am very passionate about SharePoint and love to share my SharePoint and other technology experiences.
I appreciate your compliments. Keep in touch via RSS/Email
Cheers!
Alpesh
April 5th, 2007 at 1:37 pm
What was the point of moving the database to another location on the C: drive? Normally you would move it to another totally seperate non-system violume (Such as d:)
April 5th, 2007 at 2:03 pm
Dude,
If you refer to the complete series, I have mentioned that this is in virtual environment for our production.
Secondly, I am demonstrating how you can move and not where you should move.
If that makes it clear.
April 17th, 2007 at 8:01 am
[...] This is very similiar to WSSv3 Database move from default location to another location as explained in this post. [...]
May 17th, 2007 at 2:49 am
It is not working…
This is the error I am getting when running the sql script:
HResult 0×2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
Hope there is an answer for that.
May 18th, 2007 at 10:04 pm
[...] Move WSSv3 Database [...]
June 1st, 2007 at 1:16 pm
Ion,
What stage this fails for you? Screenshot?
June 9th, 2007 at 9:41 am
It fails when I am running the script. The script does not seem to be doing anything, as the error is coming at the start of the script.
The error is displayed in the command prompt window. I am sorry but i do not have a screenshot at the moment.
June 15th, 2007 at 4:14 am
Thank you Good content.
June 19th, 2007 at 12:41 pm
Ion, did you copy the code from the post? Don’t copy. Type it in
Let me know how it goes.
July 13th, 2007 at 9:53 pm
Hi Alpesh,
I did find your site usefull, but I get an error when in the cmd prompt i type in
sqlcmd -S \.\pipe\mssql$microsoft##ssee\sql\query -E -i c:\s2.sql
The error c:\s2.sql invalid file name. Please help. I see that you do explain it further in the blog. Could you please give me the exact scripts to run when attaching and detaching. I have tried what you have there, but it does not work. Thanks Teresa
July 13th, 2007 at 10:15 pm
Hi Teresa,
Please contact me using my contact form and I will email you the scripts.
December 11th, 2007 at 2:20 am
I have two servers. Server A is currently running SharePoint v3 and SSEE on Win2k3 Std. Server B is currently running Windows Server 2000 Advanced Server. Server A is quickly running out of storage space and Server B has a few hundred Gigs of free space, hence the reason I would like to know if I could move just the database files from Server A to Server B and not incur any data loss. Is this possible if I were to map a drive letter on Server A to the directory on server B in which I move the database files? Thanks.
February 3rd, 2008 at 10:16 pm
Hi Alpesh,
I have setup the script and everything as you said. However when I run the script it says the database is in use and cannot continue. How do I ensure that it available to be detached?
Thanks
Bobb
February 7th, 2008 at 3:04 am
When I run the script the SQLCMD window opens but then disappears.
February 7th, 2008 at 9:56 pm
Guys, I am going to re run this script and may make changes to the post. Keep an eye out.
April 25th, 2008 at 4:29 am
Many thanks! The trick for me was to remove the ‘pause’ between restarting the database and the re-attachment script to avoid the database being locked if it took too long in between.
July 31st, 2008 at 3:08 pm
Hi Alps,
I got the following error when trying to run the sqlcmd command.
Msg 3703, Level 16, State 2, Server SHAREPOINTSVR1\MICROSOFT##SSEE, Line 1
Cannot detach the database ‘WSS_Content’ because it is currently in use.
Msg 1801, Level 16, State 3, Server SHAREPOINTSVR1\MICROSOFT##SSEE, Line 1
Database ‘WSS_Content’ already exists.
Pls advice.
August 27th, 2008 at 9:06 pm
PLEASE COULD YOU EXPLANE TO ME REGARDING HOW I CAN FIND THE EXACT NAME OF EXEC sp_detach_db @dbname = ‘WSS_Content’, I NEED TO KNOW SHALL I CHANGE @DBNAME ONLY OR DB@DBNAME, AND SHAL I LEAVE WSS_CONTENT AS A SAME
I NEED YOUR HELP PLEASE
September 24th, 2008 at 6:03 am
SharePoint experts:
I have moved my SharePoint databases to the domain controllers D: drive. The MSSQL$MICROSOFT##SSEE database is generating the following events in the event log on my server:
Event Type: Information
Event Source: MSSQL$MICROSOFT##SSEE
Event Category: (2)
Event ID: 2803
Date: 9/23/2008
Time: 3:38:22 PM
User: N/A
Computer: CHIPPER3
Description:
The description for Event ID ( 2803 ) in Source ( MSSQL$MICROSOFT##SSEE ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 1, Bound Trees.
Data:
0000: f3 0a 00 00 0a 00 00 00 ó…….
0008: 19 00 00 00 43 00 48 00 ….C.H.
0010: 49 00 50 00 50 00 45 00 I.P.P.E.
0018: 52 00 33 00 5c 00 4d 00 R.3.\.M.
0020: 49 00 43 00 52 00 4f 00 I.C.R.O.
0028: 53 00 4f 00 46 00 54 00 S.O.F.T.
0030: 23 00 23 00 53 00 53 00 #.#.S.S.
0038: 45 00 45 00 00 00 07 00 E.E…..
0040: 00 00 6d 00 61 00 73 00 ..m.a.s.
0048: 74 00 65 00 72 00 00 00 t.e.r…
Event Type: Information
Event Source: MSSQL$MICROSOFT##SSEE
Event Category: (2)
Event ID: 2803
Date: 9/23/2008
Time: 3:38:22 PM
User: N/A
Computer: CHIPPER3
Description:
The description for Event ID ( 2803 ) in Source ( MSSQL$MICROSOFT##SSEE ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 1, SQL Plans.
Data:
0000: f3 0a 00 00 0a 00 00 00 ó…….
0008: 19 00 00 00 43 00 48 00 ….C.H.
0010: 49 00 50 00 50 00 45 00 I.P.P.E.
0018: 52 00 33 00 5c 00 4d 00 R.3.\.M.
0020: 49 00 43 00 52 00 4f 00 I.C.R.O.
0028: 53 00 4f 00 46 00 54 00 S.O.F.T.
0030: 23 00 23 00 53 00 53 00 #.#.S.S.
0038: 45 00 45 00 00 00 07 00 E.E…..
0040: 00 00 6d 00 61 00 73 00 ..m.a.s.
0048: 74 00 65 00 72 00 00 00 t.e.r…
Event Type: Information
Event Source: MSSQL$MICROSOFT##SSEE
Event Category: (2)
Event ID: 2803
Date: 9/23/2008
Time: 3:38:22 PM
User: N/A
Computer: CHIPPER3
Description:
The description for Event ID ( 2803 ) in Source ( MSSQL$MICROSOFT##SSEE ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 1, Object Plans.
Data:
0000: f3 0a 00 00 0a 00 00 00 ó…….
0008: 19 00 00 00 43 00 48 00 ….C.H.
0010: 49 00 50 00 50 00 45 00 I.P.P.E.
0018: 52 00 33 00 5c 00 4d 00 R.3.\.M.
0020: 49 00 43 00 52 00 4f 00 I.C.R.O.
0028: 53 00 4f 00 46 00 54 00 S.O.F.T.
0030: 23 00 23 00 53 00 53 00 #.#.S.S.
0038: 45 00 45 00 00 00 07 00 E.E…..
0040: 00 00 6d 00 61 00 73 00 ..m.a.s.
0048: 74 00 65 00 72 00 00 00 t.e.r…
Could someone please explain to me what I need to do to curtail these event messages. I get thousands of these. Thanks!
January 19th, 2009 at 2:35 am
[...] Loss of data: urgent question You could have used the technique specified here http://alpesh.nakars.com/blog/wssv3-on-a-dc-4/ Might want to bookmark for your reference. — Alpesh Nakar blog: http://alpesh.nakars.com [...]