We have switched to a new CAD package at work (elecworks) which uses MS SQL Server to store it's data. Unfortunately, it seems to use a separate database for each drawing that is created. We are currently creating new drawings every day, which presents us with a backup/restore problem.
We use Mozy Pro for backup, which supports VSS. This allows us to go in and select the databases we want to backup, and will restore these directly back to SQL Server if we ever need to. Unfortunately this means we need to regularly review the list and select the new databases that have been created - it cannot add them manually or monitor SQL server for new databases.
Our other option is to use a script to backup the databases to a file, then backup these files through mozy. This then presents us a problem with restoring - we would need to manually restore every single database file, one at a time. This is going to be quite a task.
Is there an easier way of backing up an entire instance of MS SQL Server, that is easy to restore?
I just ran across an article regarding Amazon's AWS service and the very robust backup and recovery options available at the flick of a few buttons. Short of that you may want to hire out the scripting of recovery if you don't have the expertise. In the past, I've been a part of and personally written scripts that do just that. Once in place, the scripts can be created when the backups are taken and offloaded to a text file for later use.