Problem with restoring Sharepoint 2007 from backup

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jamorlandoAuthor Commented:
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.
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

zephyr_hex (Megan)DeveloperCommented:
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.
jamorlandoAuthor Commented:
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?
zephyr_hex (Megan)DeveloperCommented:
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.
jamorlandoAuthor Commented:
It's WSS.
The path is:
C:\Program Files\Microsoft SQL Server\MSSQL10.SHAREPOINT\MSSQL\DATA
zephyr_hex (Megan)DeveloperCommented:
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).
jamorlandoAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.