Although the actual files that run Rock can be backed up with normal tools (Shadow Copies, Windows Server Backup, as well as commercial software), most of your actual data is stored in the SQL database and is a little harder to back up.
If you don't have a backup solution that is database-aware, I recommend running a script every night that creates a backup of your data as a file. Assuming you're using at least SQL Server Management Studio (included in SQL Server Express with Tools), this is easy enough.
Assuming a server name of "churchSQL01", a database name of "rock" and that you want to back up the database to a single file stored at D:\\RockBackups\\rock.bak, it can be as easy as creating a file called "D:\\RockBackups\\backup_script.sql" with the following contents:
BACKUP DATABASE [rock] to DISK = 'd:\\RockBackups\\rock.bak' WITH NOFORMAT, INIT, NAME = N'rock-Full Database Backup', SKIP, NOREQIND, NOUNLOAD, STATS = 10
And then set a scheduled task to run every day to run
"C:\\Program Files\\Microsoft SQL Server\\Client SDK\\ODBC\\110\\Tools\\Binn\\SQLCMD.EXE" -S churchSQL01 -i "D:\\RockBackups\\backup_script.sql"
But maybe you want a few more features, such as
- A basic amount of logging
- The ability to keep a week's worth of backups instead of just "last night"
- Verification of the backups
Here's a SQL routine I cobbled together from various sources (sorry I don't remember them now) - set your backup_script.sql file to contain the following instead:
DECLARE @dest nvarchar(255)
SET @dest = 'd:\\RockBackups\\rock_' + CAST(DATEPART(weekday, GETDATE()) AS nvarchar(1)) + '.bak'
PRINT CAST(GETDATE() AS nvarchar) + ' - BACKUP STARTED TO FILE: ' + @dest
BACKUP DATABASE [rock] TO DISK = @dest WITH NOFORMAT, INIT, NAME = N'rock-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
DECLARE @dest nvarchar(255)
SET @dest = 'D:\\RockBackups\\rock_' + CAST(DATEPART(weekday, GETDATE()) AS nvarchar(1)) + '.bak'
PRINT CAST(GETDATE() AS nvarchar) + ' - VERIFICATION STARTED ON FILE: ' + @dest
DECLARE @backupSetId as int
SELECT @backupSetId = position FROM msdb..backupset
WHERE database_name=N'rock' AND backup_set_id=(select max(backup_set_id) FROM msdb..backupset where database_name=N'rock' )
IF @backupSetId IS NULL BEGIN raiserror(N'Verify failed. Backup information for database ''rock'' not found.', 16, 1)
RESTORE VERIFYONLY FROM DISK = @dest WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
This gets the day of the week (1-7) and appends that number to the backup. That way you've got a rolling window of a week's worth of backups at any given time. Then create a batch file
run_backup.bat in your RockBackups directory with the following contents:
"C:\\Program Files\\Microsoft SQL Server\\Client SDK\\ODBC\\110\\Tools\\Binn\\SQLCMD.EXE" -S churchSQL01 -i "D:\\RockBackups\\backup_script.sql" >> log.txt
Change your scheduled task to run that batch file instead
and it will create/append a log.txt file so you can review your backup/verification processes whenever you want. (Be sure to set "Run in" to d:\\RockBackups or your log file will go into c:\\windows\\system32)
Assuming you DO have a regular file backup solution, perhaps something with versioning, you can imagine how useful this is- you could conceivably have access to backups for much longer than would even be useful, now that your database is stored in a simple file.