[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3044
  • Last Modified:

Veritas Won't Restore Sharepoint Database

Wasn't sure where to ask this question:

http://www.experts-exchange.com/Operating_Systems/Windows_Server_2003/Q_21386395.html

If it needs to be moved, mods please move it.

I'm running Veritas 9.1 on Small Business Server 2003.  I can restore master, model, and tempdb, but msdb, STS_Config and STS_EDI-sERVER1_1 won't restore.  Had no problem doing this for our main SQL database so I'm not sure what I'm doing wrong for the sharepoint database.  Here's what I posted in the other question:

We just recovered from a complete raid 5 meltdown on our SBS 2003 server.  We have most things up and running now, thanks to a complete backup using Veritas 9.1 and restore using the IDR disk created by Veritas.

However, I'm having trouble restoring the Sharepoint Database.  I've tried restoring with various services turned off/on with varying results.  If the MSSQL$SHAREPOINT service is running, I can at least restore the master database.  BUT, I cannot restore the other database files (STS_EDI-SERVER1_1 and STS_Config).  The errors I get in the restore:

   An error occurred on a query to database STS_EDI-SERVER1_1.
   Database 'STS_EDI-SERVER1_1' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

   An error occurred on a query to database STS_Config.
   Database 'STS_Config' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

The files are not there to begin with and the sql error log seems to reflect this, but there are no log entries during the time of the restore:

2005-04-12 15:58:25.33 server    Microsoft SQL Server  2000 - 8.00.884 (Intel X86)
     Nov 29 2003 20:52:47
     Copyright (c) 1988-2003 Microsoft Corporation
     Desktop Engine (Windows) on Windows NT 5.2 (Build 3790: )

2005-04-12 15:58:25.33 server    Copyright (C) 1988-2002 Microsoft Corporation.
2005-04-12 15:58:25.33 server    All rights reserved.
2005-04-12 15:58:25.33 server    Server Process ID is 7344.
2005-04-12 15:58:25.33 server    Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\LOG\ERRORLOG'.
2005-04-12 15:58:25.34 server    SQL Server is starting at priority class 'normal'(4 CPUs detected).
2005-04-12 15:58:25.34 server    Performance monitor shared memory setup failed: -1
2005-04-12 15:58:25.70 server    SQL Server configured for thread mode processing.
2005-04-12 15:58:25.70 server    Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2005-04-12 15:58:25.78 spid3     Starting up database 'master'.
2005-04-12 15:58:25.83 spid3     1 transactions rolled back in database 'master' (1).
2005-04-12 15:58:25.83 spid3     Recovery is checkpointing database 'master' (1)
2005-04-12 15:58:25.89 server    Using 'SSNETLIB.DLL' version '8.0.880'.
2005-04-12 15:58:25.89 spid5     Starting up database 'model'.
2005-04-12 15:58:25.89 spid3     Server name is 'EDI-SERVER1\SHAREPOINT'.
2005-04-12 15:58:25.89 spid8     Starting up database 'msdb'.
2005-04-12 15:58:25.89 spid9     Starting up database 'STS_Config'.
2005-04-12 15:58:25.89 spid10    Starting up database 'STS_EDI-SERVER1_1'.
2005-04-12 15:58:25.89 server    SQL server listening on Shared Memory.
2005-04-12 15:58:25.89 server    SQL Server is ready for client connections
2005-04-12 15:58:25.89 spid8     udopen: Operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\MSDBData.mdf.
2005-04-12 15:58:25.89 spid8     FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\MSDBData.mdf for virtual device number (VDN) 1.
2005-04-12 15:58:25.89 spid9     udopen: Operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\STS_Config.mdf.
2005-04-12 15:58:25.89 spid9     FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\STS_Config.mdf for virtual device number (VDN) 1.
2005-04-12 15:58:25.89 spid10    udopen: Operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\STS_EDI-SERVER1_1.mdf.
2005-04-12 15:58:25.89 spid10    FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\STS_EDI-SERVER1_1.mdf for virtual device number (VDN) 1.
2005-04-12 15:58:25.89 spid9     Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\STS_Config.mdf' may be incorrect.
2005-04-12 15:58:25.89 spid8     Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\MSDBData.mdf' may be incorrect.
2005-04-12 15:58:25.89 spid10    Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\STS_EDI-SERVER1_1.mdf' may be incorrect.
2005-04-12 15:58:25.95 spid5     Clearing tempdb database.
2005-04-12 15:58:26.12 spid5     Starting up database 'tempdb'.
2005-04-12 15:58:26.14 spid3     Recovery complete.
2005-04-12 15:58:31.39 spid51    Using 'xplog70.dll' version '2000.80.880' to execute extended stored procedure 'xp_msver'.
2005-04-12 15:58:31.70 spid51    DBCC CHECKDB (master) executed by RORC2\administrator found 0 errors and repaired 0 errors.

I would have figured the backup would have these files to begin with and be able to restore them with no problems.

Any ideas?
0
djharris
Asked:
djharris
  • 12
  • 10
1 Solution
 
gpriceeeCommented:
"2005-04-12 15:58:25.89 spid8     udopen: Operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\MSDBData.mdf."

You'll be happy that it looks pretty simple.
Check the above path to ensure that it exists.  If not, create the path: C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\
I'm sending this immediately in case you are waiting, in the meantime, I'll check on some other things.
0
 
gpriceeeCommented:
Are you using the SQL agent?
0
 
gpriceeeCommented:
Also, have you tried
Start --> All Programs --> Sharepoint Portal Server --> Sharepoint Portal Server Data Backup and Restore

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
djharrisAuthor Commented:
Thank you for your replies.

Yes, I'm using the SQL agent.  That file was not backed up as part of the file system backup (even though it was never directly excluded, either).  The sharepoint database has always been backed up using the SQL agent, not the volume shadow copy, while our other databases have been backed up using both methods (pretty much a default "backup all" job with only a few exclusions (ex: we exclude the massive cd image directory we keep from tape and instead back it up to an external USB drive).

I went back a few tapes to verify this was the case.  Shouldn't I still be able to restore via the SQL agent?

Also, you mentioned the Sharepoint Portal backup/restore utility.  I never used this, always expecting Veritas was doing it's job since I can see the databases backed up.
0
 
gpriceeeCommented:
What about the paths?
Do they exist?
0
 
gpriceeeCommented:
In BE, on the left-hand side,
Restore --> Properties --> Destination --> Sharepoint Redirection

Did you set any options?

I'm a bit short on resources right now because I'm at home.
0
 
gpriceeeCommented:
Hello.  I did a little research on restoring Sharepoint and beleive I found the issue.
Following is a link to Microsoft--the article might not apply directly . . . but the resolution does.
It appears that you need to recreate the Sharepoint Config database prior to restoring.

Microsoft link: http://support.microsoft.com/?id=831088

Pertinent quote:
"WORKAROUND
To work around this problem, remove the server topology settings, delete and re-create the configuration database, reassign the server topology settings, and then restore the portal site data from backup. To do so, follow these steps.

Note To follow these steps, you must have a backup copy of SharePoint Portal Server data that you can use to restore the portal site.
1. Start SharePoint Central Administration.
2. Remove the components that are active on the server. To do so, follow these steps: a.  Under Server Configuration, click Configure server topology.
     b.  On the Configure Server Topology page, click Change Components.
     c.  On the Change Component Assignments page, in the Component Assignment area, click to clear the Web check box, the Search check box, and Index check      
          box (if the check boxes are selected).
     d.  In the Job Server Component area, in the Job server box, click none.
     e.  Click OK, and then click Close.
3. Stop, and then restart the MSSQLSERVER service. To do so, follow these steps: a.  Click Start, point to Administrative Tools, and then click Services.
     b.  In the right pane, right-click MSSQLSERVER, and then click Stop.
     c.  In the right pane, right-click MSSQLSERVER, and then click Start.
4. In SQL Server Enterprise Manager, delete the configuration database (and any other databases that are associated with the portal). To do so, follow these steps: a.  
    Start SQL Server Enterprise Manager.
     b.  Expand Databases.  
     c.  Right-click the name of the configuration database, and then click Delete.
     d.  When you are prompted to confirm the deletion, click Yes.
     e.  Repeat steps 4c and 4d to delete any other databases that are associated with the portal site, including the profile database and the component settings
          database. For example, delete the following databases (if the databases are present): • SiteName_PROF • SiteName_SERV
5. Re-create the configuration database. To do so, follow these steps: a.  Start SharePoint Central Administration.
     b.  On the Specify Configuration Database Settings for ServerName page, in the Database Connections area, click Disconnect from configuration database, and then
          click OK.
     c.  On the Configure Server Farm Account Setting page, in the Default Content Access Account area and in the Portal Site Application Pool Identity area, specify the
         appropriate account settings, and then click OK.
     d.  On the Specify Configuration Database Settings for ServerName page, in the Database Connections area, click Create configuration database.
     e.  In the Configuration Database Server area, in the Database server box, specify the name of the server that is running Microsoft SQL Server.
     f.  In the Configuration Database Name area, specify the name of the configuration database, and then click OK.
6. On the Configure Server Farm Account Settings page, specify the settings that you want in the Contact E-mail Address area and in the Proxy Server Settings area,
    and then click OK.
7. On the Configure Server Topology page, click Change Components.
8. On the Change Components Assignments page, specify the components that are active on the server. To do so, follow these steps: a.  In the Component Assignment
    area, click to select the check boxes that you want.  
     b.  In the Job Server Component area, in the Job server box, click the name of the server.
     c.  Click OK, and then click Close.
9. Restore the portal site from backup. For more information about how to restore SharePoint Portal Server data, see the "Backup and Restore" topic in the Microsoft
    Office SharePoint Portal Server 2003 Administration Guide. "
0
 
djharrisAuthor Commented:
Thank you, will try that today/tonight and post results.
0
 
djharrisAuthor Commented:
Hmm...I'm wondering if my Sharepoint Central Admin is crippled in some way because of SBS.  I don't have many of the selections mentioned.  Under Server Configuration I don't see Configure Server Topology.  Here's everything I have available:

Virtual Server Configuration  
 Extend or upgrade virtual server
 Create a top-level Web site
 Delete site collection
 Configure virtual server settings
 
Security Configuration  
 Set SharePoint administration group
 Manage site collection owners
 Manage Web site users
 Manage blocked file types
 Configure antivirus settings
 
Server Configuration  
 Configure default e-mail server settings
 Manage Web server list
 Set default content database server
 Set configuration database server
 Configure HTML Viewer
 Configure virtual server for central administration

Component Configuration  
 Configure full-text search
 Configure usage analysis processing
 Manage quotas and locks
 Configure data retrieval service settings

I went into "Set configuration database server" and there's a place to change the name of the database but it doesn't seem to *do* anything when I change it.
0
 
gpriceeeCommented:
Can you start at step 5 b ?
0
 
djharrisAuthor Commented:
There's no database control options either.  I did just succeed in one area, though.  I tried restoring JUST the STS_CONFIG  database and that worked!  Now I get a little further in Server Configuration in that I can click on Set default content database server (before it would tell me "unable to connect to database" or something like that).  Now I go into another screen where I can specify a Content Database Server, but again it looks like changing the field doesn't cause anything to realy happen.
0
 
gpriceeeCommented:
0
 
djharrisAuthor Commented:
No, I don't have either installed.  We also have just purchased v10 but I don't know if it's better to install the service pack or just upgrade completely.  Any guess?
0
 
djharrisAuthor Commented:
Acvtually let me clarify.  I don't have SP2 installed but my rev is at 4691, so I don't think the hotfix applies.
0
 
gpriceeeCommented:
Install SP2 and skip the hotfix.
0
 
djharrisAuthor Commented:
Okay, just installed SP2 but it insists on restarting server...that will have to wait 'til noon.  I will update then.
0
 
djharrisAuthor Commented:
Still can't restore.  SP2 is installed.  I've tried this with/without the Sharepoint Timer Service running and with/without the companyweb web site started.  There's also 2 other web sites.  "Microsoft SharePoint Administration" and "SharePoint Central Administration" that were running.  I'm going to stop them and try to restore again.  Anything else you can think of that might be holding the database open?  I can't rename the directory, it seems, under any circumstances, so that tells me something still has it open, even with ???ALL??? the sharepoint services stopped.

My last try will be to do the same with exchange and IIS completely stopped as well, but I can't try that until after hours, probably tomorrow.
0
 
gpriceeeCommented:
Hello.  I found a Veritas document about restoring Sharepoint from the SQL agent without the Sharepoint agent.
http://eval.veritas.com/downloads/pro/backup_exec/bews_91_sps_2003_wp.pdf

The restoration process begins on page 11.
0
 
djharrisAuthor Commented:
Okay...I have the database restored, after a bit of support from Veritas (it threw the tech that these were not "standard" sharepoint files).  First, what I did wrong the first time around was I tried to restore in one operation, and should have instead done multiple restores.  The first time around I did not choose just master and select the automated master restore.  If I had done this, then I would only have needed to restore the user databases on a second restore pass.

To get around all of this mess, we did some trickery at the file system level.  We copied the msdbdata.mdf from the original SQL installation cd so SQL would at least see a file for it.  After restarting SQL msdb was no longer suspect.  At this point I could also delete the SERVER1 file and create a new database (same name) within Enterprise Manager.  Now the files are all there, so we can go back and restore first master and then the user files.  

First we restored master with these things checked under the SQL options:  replace database or file groups, automate master database restore, restore all database files to the target instance's data location.  Then, just the user database files with all the same options except the automate master database restore.

WHEW!

Oh, and all this was done with the sharepoint web sites offline as well as the timer service stopped.  Dunno if those made a difference.

I guess the bottom line is that I didn't fully understand how to restore SQL, sharepoint or not.  I didn't need to do a tape restore of our other SQL database because I had been able to manually back it up to another server just prior to the crash, and that data was more recent than the tape.  Between that and the other sharepoint services grabbing hold of the directory made the whole task a bit more complicated as well.

I'd still like to reward points since you stuck it out with me.  Please post a follow-up comment referencing this one and I'll reward there so that other's see the final solution properly.

Thanks for sticking with me.
0
 
gpriceeeCommented:
Woohoo!  Glad to see you're out of the mess.

Restoring SQL can be challenging.

I like that msdbdata.mdf copy and will remember it.

I learned how to restore the master under presssure.  I restore the master from a SQL backup rather than a Veritas backup.  If you have the room, you can dump the master to another location so that you have it in case of emergency.  

Now, I have a pizza box that I swap out drives on and practice restoring SQL--among other things; however, I have not attempted to restore SQL after I adding Sharepoint but will do so shortly--time is killin' me.  My concern is that no one from Veritas touts the Sharepoint module: "All you need is to be able to restore SQL" they say, so I don't have the Sharepoint module either.
0
 
djharrisAuthor Commented:
Yeah Veritas does so many weird things. I'm guessing the sharepoint module just automates the need to turn off the sharepoint dependencies during the restore process.

I've just added maintenance plans for a weekly backup of the system databases, for both our primary and sharepoint databases.  Thanks for the suggestion!  When you say "dump" the master...are you doing something different than a backup?  I know I can copy the files when the service is stopped, but it would be nice to automate this too.
0
 
gpriceeeCommented:
Hi.
What I mean by dump the master is to back it up within SQL itself.  Actually, because I have the space, I backup (dump) all of my databases through SQL and prefer to restore through SQL.  Veritas is my backup backup software.

If you setup a maintenance plan in SQL, you can set it for all databses or specific ones.

If you want to stop the databases automatically, use Veritas help to check out pre and post commands.  They can stop SQL before the backup and then restart it afterward.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now