Thursday, January 15, 2009

STSADM Restore Errors With SQL SERVER Express

But There's Tonnes of Disk Space!

The other day I was migrating a site collection from one machine to another. I had a copy of the source site collection and was ready to restore it on this new machine that another developer had prepped. I created a SharePoint web application at the destination and then proceeded to run the restore command:

stsadm -o restore -url http://someurl -filename c:\file.bak -overwrite

After a while I ended up getting some disgruntled feedback from the stsadm tool:

The site collection could not be restored. If this problem persists, please make sure the content databases are available and have sufficient free space.

Which was confusing, I made sure there was tonnes of disk space (20 GB free for the restore of a 3 GB site collection) and tried died with the same error. After poking around I noticed something odd after running the following query in the Management Studio:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

As it turns out, the database server I was trying to restore the site collection into was running SQL Server Express Edition which has a file size cap of 4 GB. When this "farm" was set up, it was done so with a Basic install which by defaul installs SQL Express and inherits it's limitations. The site collection I was trying to restore was breaking the 4 GB limit and as a result the stsadm tool thought there wasn't any disk space available, when really there was a bunch to be had..

Upgrading SQL Server Express to Developer/Standard/Workstation/Enterprise

As a result I had to upgrade the OFFICESERVERS instance of SQL Server running on that machine from Express to Developer (you can upgrade it to Developer/Standard/Workstation/Enterprise, whatever you have in terms of media/licensing).

This ends up being pretty easy, you simply find the media/installer and run the setup.exe that you normally would but with an added argument of SKUUPGRADE=1.

setup.exe SKUUPGRADE=1

When the install wizard gives you get a chance, select the OfficeServers instance and upgrade the database engine. If you want a more detailed walkthrough, one is available here. When you're done, run the same SQL query above and the product edition should no longer be Express.

After that small adventure, the restore worked like a charm.

Hope that helps someone.



LifeDecoder said...

hi tom trying to the same things as you describe and getting an SQL error here is the log
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Installer\InProgress 3: 2
MSI (s) (24:30) [17:23:18:152]: Note: 1: 1402 2: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Installer\InProgress 3: 2
MSI (s) (24:30) [17:23:18:152]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (s) (24:30) [17:23:18:152]: Restoring environment variables
MSI (c) (A0:D0) [17:23:18:152]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (A0:D0) [17:23:18:152]: MainEngineThread is returning 1603
=== Verbose logging stopped: 7/9/2009 17:23:18 ===

Any idea?
thank you

Anonymous said...

Hello there, i have the same problem with the restore thing, but i have sql server 2005 enterprice edition. Can you help me with that?