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?

  • 1
    it seems to use a separate database for each drawing that is created - Ummm... That sounds crazy. I'd be talking to the CAD vendor about this. – joeqwerty Jun 17 '15 at 15:57
  • elecworks should use a db per project, not per drawing, but in terms of the question. Could use not use a maintenance plan inside SQL and then just backup the bak file using mozy pro? The maintenance plan allows you to select All User Databases – Drifter104 Jun 17 '15 at 16:13
  • Drawing / Project, whichever. Either way, i'm still looking at backing up hundreds of databases. With a maintenance plan, can I also restore in one go? – Gavin Coates Jun 18 '15 at 14:04

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.