Backing up Your Rock Data

  • By Michael Garrison 2 Years Ago

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  
GO  
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)  
END

RESTORE VERIFYONLY FROM  DISK = @dest WITH FILE = @backupSetId,  NOUNLOAD,  NOREWIND  
GO  

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

D:\\RockBackups\\run_backup.bat  

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.

Backup Folder


@mikejed
Spark Development Network
Flagstaff, AZ

Michael Garrison recently left his job in Architecture to become one of the "new guys" at Spark (the "Core Team"), but he's still helping out Christ's Church of Flagstaff and other non-profits with tech needs in his off-hours, trying to make computers do what computers do best, so that people are freed to do what we do best: relate with people!