Regular backup and archival options for a client device that runs MS SQL Express including restores

Question

In the current environment, a client application has been deployed on a single endpoint device e.g. laptop. The client application records & retrieves information that is needed for regulatory and compliance purposes and as it stands today, there is no backup of the data.

Since the data is deemed critical for regulatory and compliance reporting, backup and archival options are being explored.

What available options exist for backing up, archiving and restoring the client database?

Environment

1. Client device runs off Windows 7
2. MS SQL Express 2008

Requirements

1. RTO - 24 hours
2. RPO - 15 minutes

Answer 1

I'm going to assume your budget is limited (because it always is, isn't it?).

Head on over to Ola Hallengren's site and grab a copy of his maintenance solution. Run the script after you've configured the basics:

SET @BackupDirectory     = N'C:\Backup' -- Specify the backup root directory.
SET @CleanupTime         = NULL         -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.

Since you are using Express edition, you won't have the SQL Agent that allows you to schedule tasks, so use the windows task scheduler to set up your backups. Because your RPO is 15 minutes, I would recommend 1 daily full (at midnight for example) and a log backup every 5 minutes. Make sure your databases are in FULL.

A typical job would look like this:

sqlcmd -E -S MYSERVER\MYINSTANCE -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'ALL_DATABASES', @Directory = N'F:\SQLBackup', @BackupType = 'FULL'" -b

(schedule this every day)

sqlcmd -E -S MYSERVER\MYINSTANCE -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'ALL_DATABASES', @Directory = N'F:\SQLBackup', @BackupType = 'LOG'" -b

(schedule this every 5 min)

While you are using this solution, look into the other possibilities for updating statistics and index maintenance, ...

When restoring, restore the FULL and all following LOG-backups in order. Be sure to restore with NORECOVERY until you have applied the last logfile. Because there might be a lot of files, you might want to look into scripting this. Look here for more info on that.

Now comes the hard part. If the machine dies, you still won't get your RPO, so you need to get the backups off the machine (and maybe even to a place that is geographically far away). There are a ton of options here.

What I usually do is just back it up to a different data center/machine with the backup tool that is used in the environment for flat file backups. If there is none, you might want to look into getting maybe an Amazon Web Services account and move it to S3. If you are worried about security, you can encrypt your backups with applications like Arq.

So, final: RTO. This can be tricky, depending on how you look at it. Do you have a team that can deploy a new SQL Server if needed and restore the backups (from AWS)? Is there an on call rotation system? are you going to pick up the phone 24/7?

If you want to automate this, things get a lot more complicated and you will need additional servers for sure. Since you are on 2008, you could set up log shipping. This is a feature in SQL Server that you can use for free with every possible version. It makes a copy of the log files and restores them to your secondary sql server. I can elaborate if you want.

Please, make sure to test your solution and monitor if backups are running good! Nothing sucks more than having to do a restore and noticing at that time the backup hasn't run in 5 days. If it needs to be cheap, mailing the results works, but that gets old really fast. Maybe look into a monitoring system like Icinga if money is an issue.

PSA: I'm not affiliated with any of the solutions I propose here, it's just what I do usually when I come across this issue.

Answer 2

I think you're conflating high availability with back ups. They're two different things. Always On Availability groups aren't related to backing up the SQL database. You also don't need to backup the database to a SQL farm (whatever it is that you think a SQL farm is). You can simply script a database backup at intervals that meet your RPO on the client machine where SQL Server Express is installed.

Answer 3

MSSQL Express version lacks of SQL Server Agent. You can use a combination of OSQL Utility (command line utility) and Windows Scheduler to execute t-sql BACKUP DATABASE statement. You can backup to external location (if mssql process account allows for it). You may use FULL restore mode and combination of BACKUP DATABASE / BACKUP LOG to have kind of continuous backup (ie. log backup every 15 minutes). Do not forget about system databases (master, model, msdb).

You may use Dropbox (or similar) as an external location also. In fact dropbox folder is local one, so you won't have problems with accessing network folders (mssql windows account can limit it). MSSQL 2008 R2 may compress data on backup (not sure if express version can).