While I right away want to say to you - I'm not getting into the "what's-best-MySql-or-Sql-Server"-war with this article - instead I just wanted to tell you what happened to me and my clients lately and how surprised I was to see that both MySQL and MS SQL Server 2000 solved my problems pretty easily.
This is also a true story which points out some things to pay attention to when it comes to backing up data. The background:
a) a customer running MySQL reinstalled a server and didn't think of backing their MySQL databases up first. To their defense it didn't contain any business critical data, still I had no idea of how to -
from their old MySQL 4 binary files just in a directory on their hard disk - get it into MySQL 5 which was now installed.
As you might know MySQL uses a pretty easy to understand file handling scheme, each database is stored in a separate folder on the harddrive and each table is built up from a few files. After surfing around for a short while looking for some sort of MySQL binary reader, I thought why don't I just try to copy the whole directory into the new MySQL data directory and restart the MySQL service.
Couldn't believe my eyes when I saw that it actually worked. While mostly working with MS SQL Server where database metadata (i.e data about the databases) is stored inside the master database I was simply looking for a similar solution in MySQL. But of course - as I said - the
metadata is simply the folders.
I then learned that it's totally dynamic - you wont even have to restart the service - just copy the database directory and the server finds out about it while running. That's pretty cool!
b) This customer had maximum bad luck. On their RAID 5 server, two drives crashed at the same time - and as while this is *SO* impossible to happen it still can do. And it doesn't stop there - because of a failing backup tape station their backup tapes turned out to be impossible to read.
They are running SQL Server 2000 and I have around 50 different databases on it - historical databases stored for statistics - and luckily enough they (naturally, since each historical database weights in at around 800 MB) ran out of diskspace and therefore bought an external drive which I have used for database storage for the last few years or so.
So, the best I could do was to reinstall MS SQL Server and get those databases stored on the external drive up again.
And to the rescue here comes sp_attach_db
EXEC sp_attach_db @dbname = 'thedb',
@filename1 = 'v:\Data\thedb_data.mdf',
@filename2 = 'v:\Data\thetb_data.ldf'
To transform a totally clean MS SQL Server to one with around 20 databases of each 800 MB, it just took a few minutes.
So, from this perspective both databases really impressed me.
And last of all, while things might seem to be secure, with RAID:s and tape backups, it never hurts to have two different backup solutions - I mean
for historical databases as in my case above, creating DVD:s would have been pretty easy.