Friday, June 29, 2012

Restore Content DB–SharePoint 2010

I “accidentally” deleted my default root site on my test SharePoint 2010 farm. Oopsy. As I was testing the backup functionality yesterday I thought I might as well try to do a restore. It didn’t work out-of-the-box, although I think I got there in the end.

This is a farm installation, not the single-box installation option, but all on one box so we can add others later if necessary. It’s SharePoint 2010 on Window Server 2008 R2 (no service pack) with SQL Server 2010 R2.

The farm was built a few weeks ago and has little content or use, so should be fairly easy – it’s not like we have heavily customised it or anything.

I did a full backup yesterday, so restoring to that should bring back the site I just killed.

SharePoint restore lets me select my backup and then choose just the bit I think I need to restore: the WSS_Content database. I choose to restore it using the existing configuration, so it should overwrite the current database.

image

A few minutes later the job finishes with an error message. The log shows the database restore completed ok, but OnPostRestore failed. Pointing my browser at the root site shows it’s not there – the restore hasn’t worked.

[6/29/2012 11:20:27 AM] Verbose: Starting OnPreRestore event.
[29/06/2012 11:20:27] Verbose: Starting object: WSS_Content.
[6/29/2012 11:20:28 AM] Verbose: Starting OnRestore event.
[29/06/2012 11:20:28] Verbose: Starting object: WSS_Content.
[29/06/2012 11:20:28] Verbose: [WSS_Content] SQL Server Connection String: Data Source=SPTEST01;Initial Catalog=WSS_Content;Integrated Security=True.
[29/06/2012 11:20:28] Verbose: [WSS_Content] SQL command started at: 29/06/2012 11:20:28. This command may take a while to complete and without notification.
[29/06/2012 11:20:28] Verbose: [WSS_Content] SQL Server Command:
IF EXISTS ( SELECT * FROM master..sysdatabases WHERE has_dbaccess(name)=1 AND name=@db_name )
BEGIN
ALTER DATABASE [WSS_Content] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
END
RESTORE DATABASE [WSS_Content] FROM DISK=@db_location WITH STATS=5, FILE=1, REPLACE, NOREWIND, NOUNLOAD, RECOVERY
    @db_location=c:\backups\sharepoint\AutoWeeklyFull\spbr0000\000000B4.bak, @db_name=WSS_Content
[29/06/2012 11:20:28] Verbose: [WSS_Content] SQL command timeout is set to 1.00 hours.
[29/06/2012 11:20:30] Progress: [WSS_Content] 5 percent complete.
[29/06/2012 11:20:30] Progress: [WSS_Content] 11 percent complete.
[29/06/2012 11:20:30] Progress: [WSS_Content] 15 percent complete.
[29/06/2012 11:20:30] Progress: [WSS_Content] 21 percent complete.
[29/06/2012 11:20:30] Progress: [WSS_Content] 25 percent complete.
[29/06/2012 11:20:30] Progress: [WSS_Content] 31 percent complete.
[29/06/2012 11:20:30] Progress: [WSS_Content] 35 percent complete.
[29/06/2012 11:20:31] Progress: [WSS_Content] 41 percent complete.
[29/06/2012 11:20:31] Progress: [WSS_Content] 45 percent complete.
[29/06/2012 11:20:31] Progress: [WSS_Content] 50 percent complete.
[29/06/2012 11:20:31] Progress: [WSS_Content] 56 percent complete.
[29/06/2012 11:20:31] Progress: [WSS_Content] 60 percent complete.
[29/06/2012 11:20:31] Progress: [WSS_Content] 66 percent complete.
[29/06/2012 11:20:32] Progress: [WSS_Content] 70 percent complete.
[29/06/2012 11:20:32] Progress: [WSS_Content] 76 percent complete.
[29/06/2012 11:20:32] Progress: [WSS_Content] 80 percent complete.
[29/06/2012 11:20:32] Progress: [WSS_Content] 86 percent complete.
[29/06/2012 11:20:32] Progress: [WSS_Content] 90 percent complete.
[29/06/2012 11:20:32] Progress: [WSS_Content] 96 percent complete.
[29/06/2012 11:20:34] Progress: [WSS_Content] 100 percent complete.
[29/06/2012 11:20:34] Verbose: [WSS_Content] SQL Server Message: Processed 6528 pages for database 'WSS_Content', file 'WSS_Content' on file 1.
[29/06/2012 11:20:34] Verbose: [WSS_Content] SQL Server Message: Processed 2 pages for database 'WSS_Content', file 'WSS_Content_log' on file 1.
[29/06/2012 11:20:36] Verbose: [WSS_Content] SQL Server Message: RESTORE DATABASE successfully processed 6530 pages in 4.654 seconds (10.960 MB/sec).
[29/06/2012 11:20:36] Verbose: [WSS_Content] SQL command completed at: 29/06/2012 11:20:36.
[29/06/2012 11:20:36] Verbose: [WSS_Content] Verifying that the database restoration is complete.
[29/06/2012 11:20:36] Verbose: [WSS_Content] SQL Server Command: USE [master]
                        SELECT status FROM master..sysdatabases WHERE name=@db_name
    @db_name=WSS_Content
[29/06/2012 11:20:36] Debug: [WSS_Content] Database status is 65536.
[29/06/2012 11:20:36] Verbose: [WSS_Content] SQL Server Command: SELECT TOP 1 1 FROM [WSS_Content]..sysobjects
[29/06/2012 11:20:36] Verbose: [WSS_Content] Database restoration has been verified.
[6/29/2012 11:20:36 AM] Verbose: Starting OnPostRestore event.
[6/29/2012 11:20:36 AM] Verbose: Starting object: WSS_Content.
[6/29/2012 11:20:36 AM] FatalError: Object WSS_Content failed in event OnPostRestore. For more information, see the spbackup.log or sprestore.log file located in the backup directory.
    SqlException: Cannot open database "WSS_Content" requested by the login. The login failed.
Login failed for user 'MYDOMAIN\SharePointAdminUserName'.
[6/29/2012 11:20:36 AM] Debug:    at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SharePoint.Utilities.SqlSession.OpenConnection()
   at Microsoft.SharePoint.Utilities.SqlSession.ExecuteNonQuery(SqlCommand command)
   at Microsoft.SharePoint.SqlRemoteBlobSession.CheckRbsInstalled()
   at Microsoft.SharePoint.Administration.SPRemoteBlobStorageSettings.Installed()
   at Microsoft.SharePoint.Administration.SPDatabase.OnPostRestore(Object sender, SPRestoreInformation info)
   at Microsoft.SharePoint.Administration.SPContentDatabase.OnPostRestore(Object sender, SPRestoreInformation args)

Boo. Hiss. Out-of-the-box (OOTB) functionality not working. The account is the farm admin account which was used to install the SharePoint farm. There’s no way it doesn’t have enough rights and, sure enough, they look ok to me when viewed with SQL Server Management Studio. That account is a server admin and has the db_owner role on the database.

I try again, this time choosing to restore WSS_Content to a new name: WSS_Content2. This also fails in the same place, but with a different error message.

[29/06/2012 11:56:14] Verbose: [WSS_Content2] Database restoration has been verified.
[6/29/2012 11:56:14 AM] Verbose: Starting OnPostRestore event.
[6/29/2012 11:56:14 AM] Verbose: Current object: WSS_Content2 (previous name: WSS_Content).
[6/29/2012 11:56:15 AM] FatalError: Object WSS_Content2 (previous name: WSS_Content) failed in event OnPostRestore. For more information, see the spbackup.log or sprestore.log file located in the backup directory.
    SPException: Cannot attach database to Web application. Use the command line tool or Central Administration pages to attach the database manually to the proper Web Application.
[6/29/2012 11:56:15 AM] Debug:    at Microsoft.SharePoint.Administration.SPContentDatabase.OnPostRestore(Object sender, SPRestoreInformation args)

I guess that might be because the existing database is still configured and the new copy can’t co-exist, so I delete both the new database and the existing one using SQL Server Management Studio, so both WSS_Content2 and WSS_Content are gone. (It’s just a test box – a reinstall from scratch is possible if this doesn’t work).

This time it works, although the SharePoint Central Admin (CA) GUI is showing the previous error status with the successful restore log underneath. Still, this is the usual level of shoddyness that I expect from big fat Microsoft products so am unfazed.

No comments: