The semaphore timeout period has expired

I got this error message on my SharePoint farm backup (using the STSADM command
STSADM.EXE -o backup -directory \srv001sqlbackups -backupmethod full):

Object SharePoint_Config failed in event OnBackup. For more information, see the error log located in the backup directory. SqlException: Write on “\srv003SP_Backupspbr0001000001D.bak” failed: 121(The semaphore timeout period has expired.) BACKUP DATABASE is terminating abnormally.

My first thought was that it conflicted with another backup job, so I moved the start time for the backup several times to see if that helped. Obviously it lost connection to the SQL server. But to move the start time did not help. I googled it but did not find anything helpful and after having analysed the Event Viewer errors and the backup log error, I saw that the error message was displayed almost immediately after the backup had started. And then I realised that maybe it had to do with the time limit for the backup job instead of a timeout against the SQL server. So, I increased the time limit in Scheduled tasks (which runs my bat file) with a couple of hours and then the backup went through without any errors.

Just one of those error messages that are hard to determine the right solution for….

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.

Script that delete old backup folders

Yey! If you want to save the server administrator (speaking!) some hell by deleting old backup folders and save disk space, then this is a solution for you!

Create a new file with Notepad and name it like deletebackupfiles.vbs and paste the following code (I cannot write the code here of course, but you can copy the code from the site http://www.mssqltips.com/tip.asp?tip=1324):

Edit the number of days on the first line, that is how old files you want to delete from the backup share. Then on the next line, enter the path to your share where the backup files are.

Remove the ‘ sign before “objFile.Delete” so that the script executes the deletion 🙂
Put a ‘ sign before the MsgBox instead, or else you must press OK to every file it wants to delete.
Then, create a new file with Notepad and name that deletebackup.bat (or what ever) and enter one line to it, and that is the path to your vbs file:

c:backupscriptsdeletebackupfiles.vbs

Open Task Scheduler on your server and create a new task where you want your .bat file to start at a scheduled time. I run it the night before my fullbackup creates new folders, so that the backup share is empty.

Use a domain account on MSSQLSERVER

We had some trouble running the full backup (both from CA and stsadm), we got error messages in CA that said “Error: Object SharePoint_Config failed in event OnBackup. For more information, see the error log located in the backup directory. SqlException: Cannot open backup device…” and in EventViewer it complained that we used a Bad username or password. Started to investigate that the app pools ran with the right userid = ok, services on the sharepoint server = ok, user account that initiated the backup = ok, network share had correct permissions and that it actually was a share = ok, the app pool account/service accounts/backup account had right permissions on the SQL server = ok, and then I thought everything was checked. But not. Turns out that the MSSQLSERVER service on the SQL server was running on a local account and that account tries to access the file share where you want your backup files and that share is of course not on the same machine so – access denied for that local account! It’s all so clear when you find the error, isn’t it? 🙂 So, we changed the MSSQLSERVER to a domain account which has all rights to the network share and the databases, and now our backup is up and running!

It’s a dirty job but someone’s gotta do it….

Got this error in the Backup history:

Object SSO failed in event OnBackup. For more information, see the error log located in the backup directory. SqlException: Could not locate entry in sysdatabases for database ‘SSO’. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally.

Found out that in the Backup list there was a database called SSO that was listed for backup. But, it does not exist on the SQL server:

So, how do you edit the list of databases? I gave up searching cause I didn’t find any good answers. I wanted to be able to edit the xml file that builds the list of databases to be backed up, (like the spsbackup.xml) but that is created when you run the backup…

I did a really quick and dirty solution to this: I ended up creating an empty database called SSO on my SQL server and now, the backup runs without any errors! Sometimes you just have to be a little dirty to get things done 🙂