locked
Moving Content DB to another drive in same server RRS feed

  • Question

  • Hi,

    I tried to move the content dbs to another drive in the same Server (SQL Server 2005 SP2) following this article:
    http://blogs.technet.com/corybu/archive/2007/06/01/detaching-databases-in-moss-2007-environments.aspx

    I can see the .mdf and .ldf files in the drive, but I cannot locate the corresponding site collections. When I perform

    stsadm -o deletecontentdb...   command, I get an error which says ""This content db does not belong to this web application". I just have 2 web apps, one is portal and one mysite. I checked under Site Collection List and content db list in Central Admin, but could not locate this content db anywhere. But these dbs are accessed somehow because the modified date of the .mdf files are up-to-date. Because of this I did not move the content dbs because after moving them there is no way I can test it because I do not know which site to test.

    Any suggestions?

    Regards,
    Manoj
    Monday, February 1, 2010 5:41 AM

Answers

  • Wow, whenever you are moving dbs to another drive on the same SQL Server. In SQL Studio Manager just detach move the mdfs and ldfs to the new drive and still in SQL Studio manager Attach the dbs once they are moved... It is unnecssary to run any stsadm commands to move dbs to a different drive on the same SQL Server...

    However, if you dont want any errors en the event log of the SharePoint Server stop all of the SharePoint Services then move the dbs to the new drive as described above... Since the SQL Instance Server Name etc is not changing you do not have to remove them from SharePoint...

    Below is the SQL Query to list the FileNames and Location of all SQL Dbs... Note: change FieldID to FileID = 2 to list the LDF files


    select a.name, b.name as 'Logical filename', b.filename from sys.sysdatabases a 
    inner join sys.sysaltfiles b
    on a.dbid = b.dbid where fileid = 1

    Also, the filename of the MDFs modified date is due to the files being attached to SQL, this does not mean that you are using them in your current SharePoint environment...

    In addition prior to removing content dbs from their Web Applications or Site Collections you should know which Content dbs are required for each Web Application..

    You can also use the UI to find the content dbs and is a good way to start, you will find stsadm is faster once you start writing scripts to perform yopur administrative tasks... To use the UI to find the Content dbs your Web Applicatins are using 

    Go to Central Administration > Application Management > Content Databases > Choose the Web Application URL from the drop down > Document the web Application URL and Content DB name... 

    After you have all of the Content Dbs you can use Studio manager to set the unused Content Dbs to Offline. This will allow you to test the Sites and ensure eveything is working prior to removing eth content dbs and allow for a quick recovery by setting the dbs back online if needed...



    Cheers,


    -Ivan



    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    Monday, February 1, 2010 8:44 AM
  • I know this thread is already cold, but I'll add my guidance here for others in the future.  From reading this thread, it appears that an attempt was made to move the mdf and ldf files of a given content database to new physical drives on the same SQL Server and then things went sideways and the content databases were no longer showing up in SharePoint under the target web app, combined with many, many event log errors.

    Whenever things get this bad, it's always best to do a reset and start clean.  What I mean by that is to take a step back and approach the problem from a different angle.  The proper way to move content databases without errors are as follows:

    1. Schedule an outage for affected site collections, preferably off hours.  Communicate this outage with your users!
    2. When ready, do a full SQL Server backup of the target database.  You can never, ever have too many backups.
    3. Detach the target database from SharePoint by removing it from the "Manage Content Databases" screen in Central Admin.  The reason I prefer this instead of stopping SharePoint services on the server as Ivan recommended earlier is in order to keep other site/content up and running and also in order to prevent scheduled jobs running on other server such as app servers, from accessing the content database in question.
    4. Now fire up SQL Management Studio and detach the target database in SQL Server.
    5. Move the mdf and ldf files of the target database to their new target locations.
    6. Using SQL Management Studio, attach the target database, correcting the pointers to the new locations of the mdf and ldf files.
    7. Once the database shows online in SQL Server, return to SharePoint Central Admin and re-attach the content database.
    8. Test to ensure site access has been restored.
    9. Communicate restoration of service to your users.

    Following these steps should get you a clean move.

    Given the fact that things already went sideways in this thread's case, it's best to approach it from step 4 on as detailed above.  Doing this, should force SharePoint to attach the database as a new content database and access should be restored from that point on.


    I trust that answers your question...

    Thanks
    C

    http://www.cjvandyk.com/blog | LinkedIn | Facebook | Twitter | Quix Utilities for SharePoint | SharePoint Admin Nav Console (SPANC)

    Tuesday, November 27, 2012 5:18 PM
    Answerer

All replies

  • You dont require preparetomove command if your Moss 2007 farm is running at least the Infrastructure Update (IU) or higher, running it will cause further issues.


    I LOVE MS..... Thanks and Regards, Kshitiz (Posting is provided "AS IS" with no warranties, and confers no rights.)
    Monday, February 1, 2010 6:16 AM
  • My MOSS farm is running SP1 and no further updates/patches applied. There are lots of customizations (.wsp) that went in to this farm and we are reluctant to apply SP2 or other IU patches.

    I got no clue where to search for these site collections.

    Regards,
    Manoj
    Monday, February 1, 2010 6:20 AM
  • stsadm.exe -o enumsites  -url <virtual server URL> will work for you.

    It will list all site collections and thier corresponding databases.

    You can use te following command to get the results in a .txt file and lookout of databases and thier corresponding site collections.

    stsadm.exe -o enumsites  -url virtualserverURL  >c:\abc.xt


    I LOVE MS..... Thanks and Regards, Kshitiz (Posting is provided "AS IS" with no warranties, and confers no rights.)
    • Edited by Mike Walsh FIN Thursday, February 11, 2010 1:49 PM Bold in entire post removed
    Monday, February 1, 2010 6:25 AM
  • Wow, whenever you are moving dbs to another drive on the same SQL Server. In SQL Studio Manager just detach move the mdfs and ldfs to the new drive and still in SQL Studio manager Attach the dbs once they are moved... It is unnecssary to run any stsadm commands to move dbs to a different drive on the same SQL Server...

    However, if you dont want any errors en the event log of the SharePoint Server stop all of the SharePoint Services then move the dbs to the new drive as described above... Since the SQL Instance Server Name etc is not changing you do not have to remove them from SharePoint...

    Below is the SQL Query to list the FileNames and Location of all SQL Dbs... Note: change FieldID to FileID = 2 to list the LDF files


    select a.name, b.name as 'Logical filename', b.filename from sys.sysdatabases a 
    inner join sys.sysaltfiles b
    on a.dbid = b.dbid where fileid = 1

    Also, the filename of the MDFs modified date is due to the files being attached to SQL, this does not mean that you are using them in your current SharePoint environment...

    In addition prior to removing content dbs from their Web Applications or Site Collections you should know which Content dbs are required for each Web Application..

    You can also use the UI to find the content dbs and is a good way to start, you will find stsadm is faster once you start writing scripts to perform yopur administrative tasks... To use the UI to find the Content dbs your Web Applicatins are using 

    Go to Central Administration > Application Management > Content Databases > Choose the Web Application URL from the drop down > Document the web Application URL and Content DB name... 

    After you have all of the Content Dbs you can use Studio manager to set the unused Content Dbs to Offline. This will allow you to test the Sites and ensure eveything is working prior to removing eth content dbs and allow for a quick recovery by setting the dbs back online if needed...



    Cheers,


    -Ivan



    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    Monday, February 1, 2010 8:44 AM
  • Hi,

    We have only 2 web apps, portal and MySite. I tried the enumsites command, but could not locate the content dbs in the exported file.

    I checked Central Administration > Application Management > Content Databases > Choose the Web Application URL from the drop down > Document the web Application URL and Content DB name, the problematic content dbs are listed there but I do not know to which site collection they belong from that list.

    Thank you for that SQL query Ivan, now I know the Master and other default sql databases are there in the drive that was to be released. If I hadn't run this query you provided I would have asked the IT team to remove the drive after moving the remaining contenet dbs without checking the default SQL databases!

    For some of these content dbs, I manually found the site collection (found from global navigation/site content structure comparing db name and site title) and these site collections have my portal web app as its parent site collection, but still they are not listed when I check the content db (Central Administration > Application Management > Content Databases ). Any more suggestions?

    Regards,
    Manoj
    Monday, February 1, 2010 10:01 AM
  • Hi Manoj,

     

    I agree with Ivan. As far as I know, you can use the following methods to determine which database does your site collection belong to.

     

    Open central administration, click application management, click site collection list, click the down arrow, click change web application, select the web application that you want to configure, you will find the content database’s name.

     

    For more information about move and migrate servers and server farms (Office SharePoint Server 2007), please refer to the following article:

    Move and migrate servers and server farms (Office SharePoint Server 2007)

    http://technet.microsoft.com/en-us/library/cc262370.aspx

     

    Hope this helps.

     

    Rock Wang


    Rock Wang– MSFT
    Tuesday, February 2, 2010 8:08 AM
  • Thanks Rock Wang, I appreciate your response.

    I completely agree with Ivan too here, but something is wrong with the data displayed. I can see the files attached to my SQL Server. But I cannot figure out which site collection they belong to. I checked this under Site Collection List one-by-one, but no clues!

    Regards,
    Manoj
    Tuesday, February 2, 2010 8:37 AM
  • Hi Manoj,

    Did you try my steps? If not, please try them.

    Rock Wang
    Rock Wang– MSFT
    Wednesday, February 3, 2010 2:03 AM
  • Hi Rock Wang,

    I tried your steps; I double checked

    Open central administration, click application management, click site collection list, click the down arrow, click change web application, select the web application that I want to configure, but I could not find in any those site collections these content dbs listed.

    Thank you for pointing to that Technet article. I was trying to move a Meeting workspace to another site collection using .stp, .cmp. and .fwp; but nothing succeded. I tried export command using stsadm, still no luck. The meeting workspace was not getting migrated.

    Regards,
    Manoj
    Thursday, February 4, 2010 4:15 AM
  • Hi Manoj,

     

    Open central administration, click application management, click site collection list, click the down arrow, click change web application, select the web application that you want to configure. Please post this screenshot into the forum, you can refer to the following link to post the screenshot:

     

    Use SkyDrive to upload collected files and post screen shot/picture

    http://social.technet.microsoft.com/Forums/en/w7itproui/thread/4fc10639-02db-4665-993a-08d865088d65

     

    For the second question about moving a meeting workspace to another site collection using .stp, .cmp. and .fwp, please post it in a new thread.

     

    Thanks!

     

    Rock Wang


    Rock Wang– MSFT
    Friday, February 5, 2010 8:27 AM
  • Hi Rock Wang,

    I did select the correct web application, but my content db is not listed there.

    Can I have your email so that I can upload the screen shot and make it viewable to only you.

    Regards,
    Manoj
    Friday, February 5, 2010 10:14 AM
  • Manoj-

    When you go into Central Administration and click on Content Databases and select the corresponding web application.  Are you saying that the web application that is listed there is incorrect?  How many "Sites" are listed in the content db?

    Is it possible to take a backup of the content db in SQL and try it on another server?
    John
    SharePoint911: SharePoint Consulting
    Blog: http://www.rossonmoss.com
    Twitter: JohnRossJr

    MOSS Explained: An Information Workers Deep Dive into Microsoft Office SharePoint Server 2007
    Friday, February 5, 2010 1:33 PM
  • Hi John,

    The correct web app is listed but the content dbs I'm referring to are not listed. I have provided a screen shot here:
    http://lbrmjq.bay.livefilestore.com/y1pexBb0pFXaojvDoV3jg6Kw7Mp9AzpE9Nf8g3eIMHLEZ_E4GR4EfZImdY2hCtQ7-TzdT4mtTxg1vINzpnG1sNvy6jp5huupQa3/Content%20DBs.png

    I have a few more dbs which are not listed there in the Content Databases page in Central Admin but are there attached to my Sql Server. Now I'm trying to free up space in one of our drives which contained lots of content dbs and config dbs of our MOSS farm. I successfully moved all the dbs except these dbs which I could not map to any Site Collection. Without knowing this That was the reason I created this thread.

    Regards,
    Manoj
    Monday, February 8, 2010 6:32 AM
  • I haven't counted, but there's a hard limit of 100 content databases per web application. Do you think you have more than that?
    John
    SharePoint911: SharePoint Consulting
    Blog: http://www.rossonmoss.com
    Twitter: JohnRossJr

    MOSS Explained: An Information Workers Deep Dive into Microsoft Office SharePoint Server 2007
    Monday, February 8, 2010 1:15 PM
  • Yes, there are some more which is not getting listed there in that page. I do not know why they are not listed.

    Regards,
    Manoj
    Tuesday, February 9, 2010 4:17 AM
  • As I said there's a hard limit of 100 content databases per web application.  If you've got more than that -- that's probably why they aren't showing up.  You are most likely going to have to consolidate some of those site collections into other web applications before the missing ones will show up.


    John
    SharePoint911: SharePoint Consulting
    Blog: http://www.rossonmoss.com
    Twitter: JohnRossJr

    MOSS Explained: An Information Workers Deep Dive into Microsoft Office SharePoint Server 2007
    Tuesday, February 9, 2010 12:56 PM
  • Hi John,

    Nice observation, I didn't know 100 content dbs/webapp was a best practise, thank you!

    But we only have around 85 in that web application, so I guess those which are not showing up aren't used by SharePoint anymore.

    The Reporting Service is db is one among them which doesn't show up in the Content db. To move the Report Services db to another drive on the same server, can I just detach and attach it from SQL Server Mgmt Studio?

    Regards,
    Manoj
    Thursday, February 11, 2010 12:40 PM
  • Hi,

    I moved content dbs without using preparetomove but now event logs are full of this error every hour:

    Failure trying to synch web application 018e6ad3-10b5-44a4-845d-2c1099da2aa5, ContentDB a9c9b1c1-0ea7-4b9e-96fb-8d1f691a616c Exception message was A duplicate site ID a19ba3f7-8480-4064-9636-eefad9f7ddcd(https://portal.ust-global.com/cfs) was found. This might be caused by restoring a content database from one server farm into a different server farm without first removing the original database and then running stsadm -o preparetomove. If this is the cause, the stsadm -o preparetomove command can be used with the -OldContentDB command line option to resolve this issue.

    Do i have to run this command to fix this issue:

    stsadm -o sync -deleteolddatabases 0

    My farm is running SP1 without any other updates

    Regards,
    Manoj
    Tuesday, March 16, 2010 10:32 AM
  • I know this thread is already cold, but I'll add my guidance here for others in the future.  From reading this thread, it appears that an attempt was made to move the mdf and ldf files of a given content database to new physical drives on the same SQL Server and then things went sideways and the content databases were no longer showing up in SharePoint under the target web app, combined with many, many event log errors.

    Whenever things get this bad, it's always best to do a reset and start clean.  What I mean by that is to take a step back and approach the problem from a different angle.  The proper way to move content databases without errors are as follows:

    1. Schedule an outage for affected site collections, preferably off hours.  Communicate this outage with your users!
    2. When ready, do a full SQL Server backup of the target database.  You can never, ever have too many backups.
    3. Detach the target database from SharePoint by removing it from the "Manage Content Databases" screen in Central Admin.  The reason I prefer this instead of stopping SharePoint services on the server as Ivan recommended earlier is in order to keep other site/content up and running and also in order to prevent scheduled jobs running on other server such as app servers, from accessing the content database in question.
    4. Now fire up SQL Management Studio and detach the target database in SQL Server.
    5. Move the mdf and ldf files of the target database to their new target locations.
    6. Using SQL Management Studio, attach the target database, correcting the pointers to the new locations of the mdf and ldf files.
    7. Once the database shows online in SQL Server, return to SharePoint Central Admin and re-attach the content database.
    8. Test to ensure site access has been restored.
    9. Communicate restoration of service to your users.

    Following these steps should get you a clean move.

    Given the fact that things already went sideways in this thread's case, it's best to approach it from step 4 on as detailed above.  Doing this, should force SharePoint to attach the database as a new content database and access should be restored from that point on.


    I trust that answers your question...

    Thanks
    C

    http://www.cjvandyk.com/blog | LinkedIn | Facebook | Twitter | Quix Utilities for SharePoint | SharePoint Admin Nav Console (SPANC)

    Tuesday, November 27, 2012 5:18 PM
    Answerer