Have you ever had that feeling of dread when the power goes out and your server shuts down? You know the one where you just hope to god nothing important was lost.
I think we’ve all been there – so here’s a short list of some programs we use internally for instance-restoring our databases:
1. Red Gate SQL Backup Pro –
This is my personal favorite. It can take your entire database or just parts of it and supports scripts as well as complete backups. The restore wizard makes choosing what you need very simple, and from there it’s mostly up to you if you want to attach it to another instance or create a new database on your existing server. Prices start at about $260 USD which, for me, is well worth it as an invaluable tool.
2. SQL Backup and FTP –
A free backup solution that also supports FTP as well as local and remote backups (including the cloud). It’s simple to use and can be set to run automatically or manually. We’ve used this successfully in the past and I’d say it’s a good choice especially if you want to make sure you can automate your backups for those times when we all forget to do them.
3. The Merge Replication Feature in SQL Server –
This one actually surprised me quite a bit once I discovered it existed! You’ll have to enable Merge Replication first, which will create a script that you can refer back to whenever you need to revert to a previous version. It’s perfect for those odd times when enough changes have been made that using point-in-time restores just isn’t going to cut it. Also, if you’re using virtual servers and not keeping your full backups onsite you can run into problems with restoring them – this option circumvents that by allowing you to specify what data and log files you want to be included in the restore process (just like we would normally expect from a point-in-time recovery).
4. Tombstone Reanimator –
This is actually an oldie but goodie! You’ll need some extra software (SQL skills’ Data Compare) installed first, but once you do it gives you the ability to recover individual database records from a deleted-and-recovered table. This is obviously pretty specialized but it does come in handy sometimes and can save you lots of time if you know what you’re doing (which we do).
5. SQL Restore –
This one’s free and also worth mentioning as another simple point-in-time restore option for those times when something goes wrong with an online transaction log backup. It isn’t as configurable as some of the other options but it certainly gets the job done, and I like that they support command-line parameters as well as MMC integration which gives us Sys Admins total control over our servers even during backups.
6. SQL Backup –
This one’s also free and allows you to take full backups as well as transaction-log-only backups. We don’t use it internally, but our clients have given us great feedback on this one so I always include it in my list of recommendations when they’re looking for a quick-and-dirty solution.
7. Fb-Networking –
Another option that’s really helpful if you know what you’re doing with MySQL replication (but be careful!). If your master database goes down and causes an outage for any of your slave databases, Fb Networking will help you out by allowing you to connect two or more servers together and migrate the data from one side to the other. This makes it a lot easier to recover from a catastrophe if you know what you’re doing, but I think it’s especially helpful for smaller companies who might not have the IT staff needed to get everything back up and running within a reasonable timeframe.
8. Recover MySQL –
I would recommend this one last because it doesn’t seem to do some of the things some of the other tools we’ve mentioned can do (like automatically restoring backups, or allowing me to choose which files/data/logs go into the restore). That said, it does offer more than just point-in-time restores so depending on your needs it might be worth looking at. It also has an attractive GUI and makes it easy for us Sys Admins with no MySQL experience to get a grip on what’s going on.
Conclusion:
There are lots of other options out there as well, but my job is to know these things so I thought it might be helpful to pass along what I know when someone asks me. As with all things in IT, the tools you choose to use depend both on your needs and the level of expertise you (or your staff) have.
There are plenty more out there, so feel free to leave your own suggestions in the comments below. There are several good options out there, but finding the right software really depends on your needs and what you’re comfortable with.
