How to restore a SharePoint site using SQL database backup

This week I had to do a restore of a site collection using only SQL database backup. The customers hosting partner had not setup a SharePoint farm backup and had no test environment available that we could restore the database to, so they restored the database back to the SQL server into the production environment.
When I did an attach of the contentdb, SharePoint config would not let that database into the farm since it had the same GUID as the original database (of course, since it was a restore). We tried to run some querys on the SQL to alter the GUID, but without success. Performed these steps instead, which includes detach the original db, attach the restored db to the same web application, export the site that was deleted, detach the restored db again, attach the original db and import the subsite (which was the reason for the restore).

Iisreset /stop
Detach the current content db WSS_Intranet from the SQL Server (this is the original database)
Iisreset /start
Attach the restored content db WSS_IntranetRestored
Open the site you want to restore and copy the URL
Export the site Tools via stsadm –o export –url https://intranet/se/tools -filename d:temp_backuprestore –includeusersecurity –versions 4 overwrite (where the syntax restore is a PREFIX for the backup files and NOT a filename you create)

Iisreset /stop
Detach the restored content db

Iisreset /start
Open Central Administration, enter Content databases, remove the restored db
Content databases, add the original content db
Attach the old content db

Iisreset

Import stsadm -o import -url https://intranet/se/tools -filename “d:temp_backuprestore.cmp” -includeusersecurity -updateversions 2

Done!

SharePoint farm backup

I think I have blogged about this before, but I cannot enough emphasize the importance of running SharePoints own backup together with your SQL database backup. Of course, some third party backup products might be able to restore in the same way as you can restore a site collection backup inside Central Admin, but it IS really easy in CA to do this. It’s not that easy if you must restore a SQL database and add the content db to a new web application. What I want to restore is a single subsite inside of a sitecollection.
When using SharePoints restore option, you could easily restore a whole site collection to a new web app (either on a new server or the same) and access it by using http://server:portnumber, export the site (through stsadm or SPD) and then delete the restored web app again. Fast and easy.
But… I ran into a customer that was only using SQL db backup and some third party product, and they were only able to restore the SQL database so I had to create a new web application, detach the db that was installed default, and then add the restored db via STSADM and addcontentdb. I got a lot of errors, and it turned out that when I had attached the restored content db, and looked in “Content databases” it showed 0 sites! So, the db was attached but contained nothing. Will do a SQL hack to change the GUID to something else… and so on. A lot more work!!
So today I setup the farm backup with a .bat file and put it in Scheduled Tasks and made sure it ran correctly. Got this error:

“Object WSS_Content_MySite failed in event OnBackup. For more information, see the error log located in the backup directory. SqlException: Cannot open backup device ‘\serverxxx01backupspbr0000000001A.bak’. Operating system error 5(error not found). BACKUP DATABASE is terminating abnormally.”

If you get this error when trying to run backup in SharePoint Central Administration (or using the STSADM command for full backup) then you must do the following:

1) Logon to the SQL server and determine which account that runs the MSSQLSERVICE and add that account to your backup device (that is, the SHARE where you put your backup files) and give it “Edit” permissions.

Event id 7888 in Eventviewer

A while ago I got a lot of error messages in Event Viewer on our MOSS server. I found the following solution:

run stsadm -o sync -DeleteOldDatabases 0

and the message disappears.
What it does is enter the table “sitesynch” on the SQL server and deletes connections to any old Sharepoint config database that you may have on your server.

The entire message:

A runtime exception was detected. Details follow.
Message: A duplicate site ID 3b2fa192-1efb-4715-970f-2e550122faf0(
https://xxxx) was found. This might be caused by restoring a content database from one server farm into a different server farm without first removing the original database and then running stsadm -o preparetomove. If this is the cause, the stsadm -o preparetomove command can be used with the -OldContentDB command line option to resolve this issue.
Techinal Details:
Microsoft.Office.Server.UserProfiles.ProfileSynchronizationDuplicateSiteIDException: A duplicate site ID 3b2fa192-1efb-4715-970f-2e550122faf0(
https://xxx) was found. This might be caused by restoring a content database from one server farm into a different server farm without first removing the original database and then running stsadm -o preparetomove. If this is the cause, the stsadm -o preparetomove command can be used with the -OldContentDB command line option to resolve this issue.
at Microsoft.Office.Server.UserProfiles.ContentDBSynchronizer.RegisterSitesForSynch(Guid[] rgGuid, Int32 nGuids, Object dummy)
at Microsoft.Office.Server.UserProfiles.SynchCollection`2.FlushAdds()
at Microsoft.Office.Server.UserProfiles.SynchCollection`2.Add(T objAdd)
at Microsoft.Office.Server.UserProfiles.ContentDBSynchronizer.AddRemoveSites(String strFirstChangeToken, SPChangeToken lastChangeToken)
at Microsoft.Office.Server.UserProfiles.ContentDBSynchronizer.SynchContentDB()
at Microsoft.Office.Server.Diagnostics.FirstChanceHandler.ExceptionFilter(Boolean fRethrowException, TryBlock tryBlock, FilterBlock filter, CatchBlock catchBlock, FinallyBlock finallyBlock)