[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Problem with restoring Sharepoint 2007 from backup

Posted on 2010-08-19
Medium Priority
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
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
  • 4
  • 3
  • 2

Accepted Solution

Darksquire earned 668 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.
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

LVL 44

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 1332 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 44

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 1332 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 44

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

649 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