Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Problem with restoring Sharepoint 2007 from backup

Posted on 2010-08-19
Last Modified: 2012-08-13
I am trying to restore our production Sharepoint 2007 server into a virtual environment.
I made sure to install Service Pack 1 (v of Sharepoint, as that's what's running in the production environment.

From our production server, in Central Administration, I performed a full farm backup.  Checking the log there were 0 errors:
[8/18/2010 3:57:04 PM]: Completed with 0 warnings.
[8/18/2010 3:57:04 PM]: Completed with 0 errors.
[8/18/2010 3:57:04 PM]: Backup completed successfully.
[8/18/2010 3:57:04 PM]: ---

Then I copy the backup directory to the virtual server and from Central Administration choose:
Restore From Backup.  I point to the backup directory that I copied, choose the full farm, and choose New Configuration.  I update everything accordingly and then click OK, and the restore fails miserably.

I won't paste the whole log, but here is what I get throughout, in regards to each database:

[8/19/2010 10:04:12 AM]: Error: Object WSS_Content_Restore (previous name: WSS_Content) failed in event OnRestore. For more information, see the error log located in the backup directory.
      SqlException: The media family on device 'C:\SharepointBackup\spbr0000\0000001D.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE DATABASE is terminating abnormally.

When I go into SQL Server Management Studio (I installed 2008 on the virtual server, because that's what's currently installed on our sharepoint server), and I try to manually restore the .bak file 0000001D.bak, I get the error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


The media family on device 'C:\SharepointBackup\spbr0000\0000000D.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

Why aren't the MS SQL Server databases restoring properly?

Question by:jamorlando
  • 4
  • 3
  • 2

Accepted Solution

Darksquire earned 167 total points
ID: 33475804
Did you back it up on SQL Server 2008 and try to restore it to an earlier version, like SQL Server 2005? That is the error that you typically get in that instance.

Author Comment

ID: 33476458
Darksquire, you're most likely correct.  Sharepoint automatically installs SQL Server 2005 when you install it, so even though I upgraded to SQL Server 2008 after I installed Sharepoint, I think the instance itself is still 2005.  Is there a way to upgrade the database instance?
Sorry, I'm not the best with SQL Server stuff.

Expert Comment

ID: 33476630
You can backup on the SQL Server 2008 instance using "files and filegroups" instead of a full backup. When you try to restore in SQL 2005, use
Tasks>Restore files and filegroups>Enter all the pertinent information as to what you want to call it, where to restore from>Click the restore box>Options (top left) and enter the location where you want it restored in the window to the right. After that, if you click OK, you will probably get an error. So instead, click on "Script" at the top of the screen and it will put the T-SQL commands in a query window right behind the window you are currently looking at. Copy and past that script to here, and I think I can change it to make it work for you.

I have never used Sharepoint, however I know that normally you can use SQL Server 2008 to upgrade a previous version.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 43

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 333 total points
ID: 33476735
if your original sharepoint is standalone, then you have a special version of sql 2005.  i am not sure if you can upgrade that special version of sql to 2008.  and in your case, i think there is a better way...  by using the SQL databases themselves.

you can upgrade those databases.  you'll need to take the mdf and ldf files from the source sharepoint and copy them to the new server.  when you attach those databases to a sql 2008 instance, sql 2008 will upgrade them.

there are some other steps involved for getting your new sharepoint to connect to those db.  and you will probably not be able to connect your new sharepoint to the config db successfully.  you're better off creating new config db and just upgrading/moving the content db.

Author Comment

ID: 33477408
Basically I just started at this company about 6 months ago and the Sharepoint server was already set up here.  It has Sharepoint 2007 SP1 standalone, SQL Server 2008 Standard Edition and Project Server.  SQL Server 2005 is NOT installed at all.  When I ask them how they did this, nobody can remember.

I'll try to attach the MDF/LDF files and see if that works.  It just shouldn't have to be that way.  (Just the content database is 130GB between the MDF and LDF files so I'll be transferring for the rest of the day today)

I'd still really like to be able to either import .bak files or have the Central Administration site do it for me.

Is there a way to know which version of SQL Server a database was created in?
LVL 43

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 333 total points
ID: 33477758
when you say Sharepoint 2007, do you mean MOSS 2007 or WSS 3.0.  WSS 3.0 is the core of MOSS 2007.  but there is no "standalone" MOSS 2007, and no standalone sharepoint 2007.  only WSS has the option for standalone, in which case, it installs a special version of sql 2005 referred to as SSEE.  it is NOT sql express, and it is not sql standard.  it's just a funky little version of sql 2005.

you should be able to tell if it's SSEE by looking at the directory path of the mdf / ldf files.  what is the path?

and... regarding the size... i bet your LDF file is big ... and i bet it's set to FULL RECOVERY.  you could change the recovery model to simple and shrink that baby if you want.  might save you some time.

you will not be able to import the .bak files unless you upgrade your sql instance to 2008.  you can try giving this a shot... but it may hose sharepoint.  worst case, you may have to uninstall your original wss, reinstall it with that SSEE/standalone sql...  and restore from your .bak.  but anyway ... if you want to give it a shot...  SQL in-place upgrades are pretty easy.  just stick in the sql 2008 disc and see if it allows you to upgrade, and then cross your fingers that sharepoint didn't puke.

Author Comment

ID: 33479630
It's WSS.
The path is:
C:\Program Files\Microsoft SQL Server\MSSQL10.SHAREPOINT\MSSQL\DATA
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 33479697
then it's likely to be SSEE.  unless someone was being less than brilliant and installed WSS with SQL Express (SQL Express has limitations, and so it's crazy to choose it rather than SSEE which does not have those limitations).

Author Comment

ID: 33500911
Here's the steps I ended up taking to run my successful restore:
--Install SQL Server 2008 (when prompted, choose Sharepoint Integrated Mode Default Configuration)
--Install Sharepoint
   --Choose New farm
   --Use the database instance you created when setting up SQL Server 2008
   --Run the wizard
--Copy STSADM backup files to server
--Assign FULL security to these files
--From central administration go to Operations->Restore From Backup
   --Choose New configuration
   --Triple check ALL fields to make sure they are pointing to the correct paths on the new server.

After I did this, I was able to open restored sites from the web browser.

There is some additional configuration to do from Central Admin: ie, starting services, configuring indexing...etc....  I'm not really too worried about that right now.  Just joyed that I finally got this to work.

Thanks everyone for getting me on the right path.

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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