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:

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.
Industry Leaders: 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!

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Downgrading MS SQL 2008 R2 Enterprise to MS SQL 2005 Standard? 12 84
PolyServe for SQL server 13 44
SQL trigger 5 37
query optimization 6 23
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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