SharePoint: WSS Installation On A DC - Move Database

6 Mar, 2007  SharePoint

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:\wssv3\sqlncli.msi /qn (quite and no notifcation)

c:\wssv3\sqlserver2005_sqlcmd.msi /qn

After installation, you will find the utility installed in the following location:

C:\Program Files\Microsoft SQL Server\90\Tools\binn

The Windows Internal Database is located in %windir%\sysmsi\ssee\MSSQL.2005\MSSQL\Data

Now another script

net stop MSSQL$microsoft##ssee

mkdir c:\SharePointv3\Database

copy %windir%\sysmsi\ssee\MSSQL.2005\MSSQL\Data\WSS_Content*.* c:\SharePointv3\Database\*.*

net start MSSQL$microsoft##ssee

sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i c:\s2.sql

pause

del %windir%\sysmsi\ssee\MSSQL.2005\MSSQL\Data\WSS_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:\SharePointV3\Database.

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:\SharePointv3\Database\WSS_Content.mdf’, @filename2 = ‘C:\SharePointv3\database\wss_content_log.ldf’

go

Now delete the database.

This solution works. It works. It works!!!

Next UP: Using Configuration Wizard without the wizard!

Alpesh Nakar

I recommend related reading. Browse through the site and grab some free tech bytes. And don't forget to subscribe to my rss feeds or updates via email. Visit here for subscription options that suit you.

Related Reading

20 Comments so far »

  1. Steven Summon said,

    Wrote on March 30, 2007 @ 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 -)

  2. Alpesh said,

    Wrote on March 30, 2007 @ 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

  3. Don Murphy said,

    Wrote on April 5, 2007 @ 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:)

  4. Alpesh said,

    Wrote on April 5, 2007 @ 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.

  5. SharePoint: Move WSSv2 MSDE DB to WSSv3 | Alpesh Nakars’ Blogosphere said,

    Wrote on April 17, 2007 @ 8:01 am

    [...] This is very similiar to WSSv3 Database move from default location to another location as explained in this post. [...]

  6. Ion said,

    Wrote on May 17, 2007 @ 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.

  7. 1st Year: My Best | Alpesh Nakars’ Blogosphere said,

    Wrote on May 18, 2007 @ 10:04 pm

    [...] Move WSSv3 Database [...]

  8. Alpesh Nakar said,

    Wrote on June 1, 2007 @ 1:16 pm

    Ion,
    What stage this fails for you? Screenshot?

  9. Ion said,

    Wrote on June 9, 2007 @ 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.

  10. Database said,

    Wrote on June 15, 2007 @ 4:14 am

    Thank you Good content.

  11. Alpesh said,

    Wrote on June 19, 2007 @ 12:41 pm

    Ion, did you copy the code from the post? Don’t copy. Type it in -) Let me know how it goes.

  12. Teresa said,

    Wrote on July 13, 2007 @ 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

  13. Alpesh Nakar said,

    Wrote on July 13, 2007 @ 10:15 pm

    Hi Teresa,
    Please contact me using my contact form and I will email you the scripts.

  14. Christopher said,

    Wrote on December 11, 2007 @ 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.

  15. Bobby Murphy said,

    Wrote on February 3, 2008 @ 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

  16. John said,

    Wrote on February 7, 2008 @ 3:04 am

    When I run the script the SQLCMD window opens but then disappears.

  17. Alpesh said,

    Wrote on February 7, 2008 @ 9:56 pm

    Guys, I am going to re run this script and may make changes to the post. Keep an eye out.

  18. Pierre said,

    Wrote on April 25, 2008 @ 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.

  19. Laily said,

    Wrote on July 31, 2008 @ 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.

  20. AHMED said,

    Wrote on August 27, 2008 @ 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

Comment RSS · TrackBack URI

Leave a Comment

Name: (Required)

E-mail: (Required)

Website:

Comment: