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 ‘\serverxxx01backupspbr0000 000001A.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.