Solved

Cannot access recovered SQL databases from sharepoint

Posted on 2014-03-31
6
409 Views
Last Modified: 2014-04-10
share point services 3.0 being used.
the sql 2000 server being used for the sharepoint databased crashed and was unrecoverable.
databased were restored from back and put onto another sql server, which is sql server 2008.

sharepoint is not able to connect to the databases that are housed on the sql 2008 server. whenever a user tries to access sharepoint the following information is added to the application log on of the sharepoint server.
event id 3351
sql database login failed. additional error information from sql server is included below

login failed for user sa


at this time i am unsure if the previous sa account password is available from the sql 2000 server the databases were restored from.

not certain this is the only issue at this time but its one we need to get past to uncover anything else that may exist.

the question is what is the procedure to restore sql  databases to an alternative sql server and make them available to sharepoint.
0
Comment
Question by:jlavery
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:colly92002
Comment Utility
You need to set up your SP farm (WFE and Apps servers) to point to the new SQL backend, but this is complicated by the fact that most of the configuration settings for Sharepoint are themselves stored in an SQL DB (the Config DB) so first you need to get to a state that you can access that.
   
Can you run the Sharepoint Configuration Wizard and use it to change the SQL connection settings?
Can you even get into Sharepoint central admin?

I would assume not, so here are some resources that should help you sort this out.  You do not need to create any new Sharepoint WFE so ignore anything about settting up a new WFE etc, you simply need to reattach first the config DB and from that you can reattach your web apps and reset your service applications to use the new SQL back end.

http://www.egroup-us.com/2011/04/sharepoint-how-to-move-databases-to-a-different-sql-server-or-instance/

http://technet.microsoft.com/en-us/library/cc671616%28v=office.12%29.aspx

http://www.ungertech.com/dbrestore.html
0
 

Author Comment

by:jlavery
Comment Utility
hey.. thanks for the reply.
no cannot get the admin url, receive the "cannot connect to the configuration database" message for both client and admin urls

will review the links you sent..

thanks again.
0
 
LVL 15

Expert Comment

by:colly92002
Comment Utility
Here are some more specific discussions of re-attaching the databases using the stsadm.exe tool.  If you don't have the names of your databases documented anywhere then use SQL Management studio to see the database names and you should be able to guess which one is the config db etc.   By default content DBs ( the actual web apps/sites) have a GUID in the name.  If you don't know your original SA password then it might be an idea to start using a network account to attach to the databases rather than SA.


http://technet.microsoft.com/en-us/library/cc263422%28v=office.12%29.aspx

http://social.technet.microsoft.com/Forums/sharepoint/en-US/6b6945ea-4d7d-4c98-acb6-5b8d4995dd4c/attaching-the-config-database-in-sharepoint-2007-with-stsadm?forum=sharepointadminlegacy

http://social.msdn.microsoft.com/Forums/sharepoint/en-US/8185cb0d-2a07-4a8c-845d-0ce31435e28d/trying-to-reattach-config-db-to-wss-30-installation
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:jlavery
Comment Utility
thanks again.. so lets say i'm using the stsadm .. the command set shows connection to one database. i am told 5 where restored for sharepoint.

i have the names of them, but just wondering how i would connect more than 1 db if i had to or is that i connect to one, which allows me back into the admin area, where i can make the further adjustments?
0
 
LVL 15

Accepted Solution

by:
colly92002 earned 500 total points
Comment Utility
Sharepoint has lots of databases, including once for each web application in your farm (the actual content), and lots of others for various back end service jobs, but you are most interested in the Config DB for now because once that is connected you will be able to get into the Sharepoint Central Admin site which will allow you to configure other settings using the GUI rather than figuring out the specific stsadm command for each action.  

It is however perfectly acceptible to connect all your databases using stsadm once you have figured out how to do it.  It is the quickest way.  Stsadm.exe runs commands directly against the Sharepoint API, which is much the same as you using the GUI, so they are simply two different ways of doing the same thing.  However not every possible command is avaiable through the GUI, and I find the central admin clunky and difficult to use.
0
 

Author Comment

by:jlavery
Comment Utility
Steps implemented to resolve.

1. Setup SQL Aliasing on SharePOint server to point to the new SQL server "SQLCLUSTER02"

2. Disconnected the existing SharePoint server farm by running the SharePoint Configuration Wizard.

3. Ran SharePoint configuration wizard to connect to the old configuration database "sharepoint".

4. It failed as we weren't aware of the password for "Network Service" account.

5. Therefore, we created a new SharePoint farm by creating new SharePoint Configuration Database "Sharepoint_Config" on SQLCLUSTER02

6. Created a new host header web application with URL "http://intranet.amslgroup.com/default.aspx".

7. And attached the original content database "STS_sharepoint01_1" to the new web application with help of command "stsadm -o addcontentdb -url < > -databasename < >"

8. Started the SharePoint Search Service from Central Administration with new search database name "WSS_Search_SHAREPOINT01-VM_New"

9. Started Full crawl with help of command "stsadm -o spsearch –action fullcrawlstart"
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now