Sharepoint WSS Backup Routine

I've been dead impressed by the free Sharepoint WSS (Windows Sharepoint Services) bundled with Server 2003 R2 or available as a free download, but after spending ages building my sites and customising them, and loading them with content, I discovered the kicker...

There isn't a user-friendly way to do backups.

If you buy Sharepoint Portal Server (SPS) and use full SQL Server for the database you have a plethora of lovely tools available, but if you are cheap and just use the free version of WSS and the WMSDE data you are in a wee bit of trouble.

Not knowing anything about SQL I had quite a bit of trouble working out how to script everything I needed, and to my surprise I couldn't really Google up anything useful. So please find below my scripts, feel free to use them and post back any suggestions you have :)

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

You need two scripts, the first one includes the SQL backup command, and the second one runs the SQL backup based on the script, backs up IIS using the iisback.vbs script MS have already provided, and writes output to a log file so you only need to check in one place.

You need to copy each of these two scripts into textfiles, rename them as I have suggested, and then add your own database name and paths. I have used the Sharepoint database STS_1_DB and the backup path C:\Backup. You can easily find your Sharepoint WSS database name from your Sharepoint admin pages; Virtual Server Settings\Manage Content Databases. Drop these files into your backup directory, run them manually, check your output matches the logfile, and then simply schedule them to run nightly or as appropriate.

:::::::: backupsharepoint.sql :::::::::::::::::::::::::::::::::::::::::::::

backup database STS_1_DB to disk = 'c:\Backup\SharepointSQL-DB.bkf'
go
backup database STS_Config to disk = 'c:\Backup\SharepointSQL-DB.bkf'
go

:::::::::::: EOF ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

N.B. Note that the content and config databases are both streamed into the same backup file for convenience.

::::: backupsharepoint.bat :::::::::::::::::::::::::::::::::::::


@echo off
rem Log start
echo Backup started... > sharepointbackup.log
echo. >> sharepointbackup.log
date /t >> sharepointbackup.log
time /t >> sharepointbackup.log
rem Take copies of the old backup files
echo.
echo Archiving...
echo.
copy SharepointIIS.MD0 SharepointIIS.MD0.old /y
copy SharepointIIS.SC0 SharepointIIS.SC0.old /y
copy SharepointSQL-DB.bkf SharepointSQL-DB.bkf.old /y
del SharepointSQL-DB.bkf
rem Script to backup Sharepoint main database in MSDE
rem and to backup the related IIS configuration
echo.
echo Starting MSDE backup routine
echo.
echo. >> sharepointbackup.log
echo Starting MSDE backup routine >> sharepointbackup.log
echo. >> sharepointbackup.log
rem This command logs into MSDE as the active Windows account
rem and runs the backup command script
rem you must have IISBACK.VBS and BACKUPSHAREPOINT.SQL files
osql -E -S SHAREPOINT\SHAREPOINT -i backupsharepoint.sql -n >> sharepointbackup.log
dir SharepointSQL-DB.bkf >> sharepointbackup.log
echo.
echo MSDE Backup routine completed
echo.
echo Starting IIS backup routine
echo.
echo. >> sharepointbackup.log
echo MSDE Backup routine completed >> sharepointbackup.log
echo. >> sharepointbackup.log
echo Starting IIS backup routine >> sharepointbackup.log
echo. >> sharepointbackup.log
rem This command calls the IIS backup script
call :IISBACKUP
dir SharepointIIS.* >> sharepointbackup.log
echo.
echo IIS Backup routine completed
echo.
echo. >> sharepointbackup.log
echo IIS Backup routine completed >> sharepointbackup.log
echo. >> sharepointbackup.log
rem Log completion
echo. >> sharepointbackup.log
echo Backup completed... >> sharepointbackup.log
date /t >> sharepointbackup.log
time /t >> sharepointbackup.log
exit

:IISBACKUP
iisback /backup /b SharepointIIS /v HIGHEST_VERSION /overwrite >>sharepointbackup.log
copy %systemroot%\system32\inetsrv\metaback\sharepointiis.* c:\backup >>sharepointbackup.log

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

Watch out for linewrapping on the second batchfile. Enjoy, mes braves :)