Thursday, April 13, 2006

Restore via SPSBackup might fail when back-end is running SQL 2005

Since SPS SP2 the use of SQL Server 2005 as back-end database server is supported for Sharepoint (WSS and SPS). But when a backup is made using SPSBackup and restored again, the following message might appear both in SPSBackup as in the Eventlog: "SQL Server restore failed. Error: The operation failed on server <servername>.".

In the log which has been created in the Program Files\Sharepoint Portal Server\Logs directory the following is logged:

00012E8C CRI 00000000 00000E5C Connecting to SQL Server <servername> ...
00012ED2 CRI 00000000 00000E5C Connected to SQL Server <servername>
00012ED2 CRI 00000000 00000E5C Connected To Server
00012ED2 CRI 00000000 00000E5C Entering RestoreDB for database <portalname>1_SITE ...
00012ED2 CRI 00000000 00000E5C Entered Critical Section in RestoreDB for database <portalname>1_SITE ...
00012ED2 CRI 00000000 00000E5C Left Critical Section in RestoreDB for database <portalname>1_SITE ...
00012FCC UNK 00000000 000007F8 Overall % Completed: 0%
00013008 CRI 80045510 00000E5C GetDatabaseByName Failed. Trying to obtain path from master database.
0001313F CRI 00000000 00000E5C Command Text sent to SQL is: RESTORE DATABASE [<portalname>1_SITE] FROM DISK = N'\\backup\bk-<servername>-<portalname>1_SITE.SPB' WITH FILE = 1, NOUNLOAD , STATS = 5, RECOVERY , REPLACE , MOVE N'<portalname>1_SITE' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Data.MDF', MOVE N'<portalname>1_SITE_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Log.LDF', MOVE N'sysft_ix_<portalname>1_SITE' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Data.MDF'
0001313F CRI 00000000 00000E5C Database <portalname>1_SITE does not exist, proceeding to restore it.
0001313F CRI 00000000 00000E5C Restoring database <portalname>1_SITE ...
000131A3 CRI 80040C68 00000E5C Failed to restore the database.
000131A3 UNK 00000000 00000E5C No error information available
000131A3 UNK 00000000 00000E5C No error information available
000131A3 UNK 00000000 00000E5C Exception in BackupSQL: Source=SQLBkMgd, Message=The operation failed on server <servername>. For more information, see the Microsoft Windows Event Log on that server.
000131A3 UNK 00000000 00000E5C BackupSQL Thread setting its status to completed and exiting


After copying and pasting the query into the SQL Management Studio, SQL returns the following error:

Msg 3176, Level 16, State 1, Line 1
File 'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Data.MDF' is claimed by 'sysft_ix_<portalname>1_SITE'(65537) and '<portalname>1_SITE'(1). The WITH MOVE clause can be used to relocate one or more files.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


The above information indicates that two parameters of the query point to the same name. Investigation of the query turns out that this is the case. Both the SITE database as the index are being restored to the same MDF file:
MOVE N'<portalname>1_SITE' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Data.MDF',
MOVE N'sysft_ix_<portalname>1_SITE' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Data.MDF'

The solution to this problem is installing the hotfix KB914445. Unfortunatelly this hotfix cannot be downloaded from the Microsoft site, so you have to call Microsoft to ask them to send it to you by email. Or if you have access to the Premier site, download it there.

"Restore operation is not completed successfully when you use the SharePoint Portal Server Backup and Restore tool (Spsbackup.exe) to restore a portal site from backup

You use the SharePoint Portal Server Backup and Restore tool to back up the portal site. However, when you try to restore the portal site from backup, the operation is not completed successfully. This problem occurs if the following conditions are true:
  • You use Microsoft SQL Server 2005 as the database back end for SharePoint Portal Server 2003.

  • You configure the Enable full-text search and index component option for content that is hosted on the portal site.

  • In this situation, the restore operation is not completed successfully because of a file naming conflict that occurs in SharePoint Portal Server 2003.
    "

    [UPDATE] The mentioned hotfix has been included in the SharePoint Portal Server 2003 post-Service Pack 2 hotfix package: June 16, 2006 (KB919175)

    8 comments:

    Ravi Polepalli said...

    Presently i also faced the same problem, can any one tell me ,Where can i download this HotFix, i didnt get any information from Microsoft , how to download it

    Yorick said...

    Ravishankar,

    You can download the SharePoint Portal Server 2003 post-Service Pack 2 hotfix package. This contains the mentioned hotfix.

    Regards,

    Yorick

    Jenny Everett said...

    So to clarify. If I have SPS 2003 running on SQL 2005 and we take a backup successfully.

    Then for instance we need to do a major restore, as we haven't applied this hot fix it will fail...

    If I then apply the hot fix it will fail because the environments did not have the same hot fixes applied as when the backup was taken.

    Is this correct?

    Note: I am luckily restoring a test environment this time!

    Yorick said...

    Jennye,

    I am not really sure, but I do think you are absolutely right. Looking at the description of the patch, i think the patchlevel of SharePoint is changed. This causes SharePoint to refuse to restore because of the incorrect patchlevel. Of course the solution is to install the patch on your current SharePoint environment as well.

    Regards,

    Yorick

    Jenny Everett said...

    Yes, I am glad I am not in a situation where I haven't and I must restore.

    On another note, I have now installed the hot fix and tried to do a restore and I still receive the same error. Any ideas?

    Yorick said...

    Jenny,

    What exactly has you done from the time you installed the patch to the time you got the error message?
    And from where and to where are you restoring?

    Cheers!

    Yorick

    Jenny Everett said...

    Hi

    I am restoring from our live environment which already has the hot fix to a test environment (1 WFE with SQL cluster backend).

    I have installed the hot fix on the test WFE, still no joy.

    Thanks for your help,

    Jenny

    Yorick said...

    Jenny,

    Did you install just the patch, or the Post-SP2 hotfix package.

    Please check the versions of all files mentioned in the KB article of the Post-SP2 package (919175) to make sure the patch has been installed succesfully.

    Did you already tried to restart your servers. The KB article does not mention this, but it still is Microsoft :-)

    Regards,

    Yorick