The Old Joel on Software Forum: Part 3 (of 5) - sysadmin stuff -- live backup

sysadmin stuff -- live backup

Unless something else is going on here,
a snapshot backup of a single database file
is not good enough to give you a consistent
database backup because the database is
(typically) multiple OS files.

Also, all the backup guides will advise that
a backup is useless with regular test restores.

Matthew Hannigan
Tuesday, January 20, 2004

I'm not saying anything on the subject except that its a well known saying with "professional" system admins that you can't teach a programmer anything about system administration - until its too late.

Robert Moir
Tuesday, January 20, 2004

I just checked the website of the product and they just use the SQL Server functionality to do backups.

Joel just doesn't know what he is talking about, and that is putting in mildly.

x
Tuesday, January 20, 2004

Also, a DBMS often has open transactions and other things in-memory that need to be written to disk -- simply copying the OS file(s) does not guarantee that your DBMS will be able to recover from them if the snapshot happens to gather a half-completed transaction.

Most DBMS products have their own backup procedures, as x mentioned, and you’re far better off using that. Heck, SQL Server can be set up to automagically backup on a schedule, so you don’t have to use that goofy 3rd-party backup software.

If you insist on using OS-level backup tools make sure you quiesce the DBMS to be sure that all open xacts are committed and no new ones appear.

MR
Tuesday, January 20, 2004

I had put this in http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=104765&ixReplies=26 but since we have this topic now ...

I wondered why Joel is using special Dantz stuff to backup SQL Sever?
I always use the backup functionalities of SQL2K to backup to a local drive and then back-up the backup. The way I see it, adding an extra layer of complexity to the process by putting in some 3rd party agent does not give me any advantages, and adds a whole new level of "things that might go wrong" in the mix.
I am not a DBA (more of an allrounder by nescessity and choice), but most of the profesional SQL Server DBA's I have heard on this topic seem to agree with this.

Just me (Sir to you)
Tuesday, January 20, 2004

I backup sql to the harddrive and from there to tape. I too don't like the agents that you can buy for extra $$$ from the backup software vendors. I would trust the backup inherent to the rdbms above third party stuff. SQL Server is probably the easiest to manage rdbms there is.

Hint: Joel check out the Database Maintenance Plans in Enterprise Manager.

Mike
Tuesday, January 20, 2004

I've always been amused at the fancy SQL (and exchange svr) backup methods. I've just been using the free/built in tools to provide perfect backups at no extra cost all along.

And all this time I should have been spending more money to get a solution that probably isn't as good as my current one.

Robert Moir
Tuesday, January 20, 2004

Robert M -
What tools are you using to back up?

(or see my post, made BEFORE I read yours)

The real Entrepreneur
Tuesday, January 20, 2004

Well, in our case restoring from a file would take too long. We actually have a multi layer backup for SQL.

We have a standby server that has the most recent transaction logs applied to it once every thirty minutes. Then we also have the transaction logs backups and database backups written to tape every night.

It all depends on how mission critical your stuff is though. It's my guess that if Joel had to go a day without SQL server up and running it would be bad but not disastrous. If we go three hours without SQL server it's the end of the world (at least according to the users).

As far as the rest of the backups, I would recommend that all files be stored on a networked raid array, and that all files be backed up off site at least once per week, and have any changed files backed up off site nightly.

Steve Barbour
Tuesday, January 20, 2004

Well real Entrepreneur, we're using the inbuilt SQL backup tool to generate a database backup file which we then backup as a normal file as part of a backup of that whole server, using the basic veritas backup tool.

With Exchange, we use NTBackup (which is the tool best supported by MS for doing backups of Exchange) to generate a backup file for the exchange stuff, and we then backup that file with veritas.

In both cases, this is very loosely and in a greatly scaled down way modelled on the method that Microsoft's own Ops dept. use to backup databases - I figure they should know the good ways to backup their own product!

Robert Moir
Wednesday, January 21, 2004

"Well real Entrepreneur, we're using the inbuilt SQL backup tool to generate a database backup file which we then backup as a normal file as part of a backup of that whole server"

Same here.

Just me (Sir to you)
Wednesday, January 21, 2004

I am the DBA for a company that has about 40 customers running SQL Server and I advise them to use the native SQL backup. We don't support any other backup software, and frankly we don't see the need to. The native backup backs up to disk, and the files from that disk are then backed up by the customers file backup program. That gives a spare set of backups on the server it self as well.

x
Wednesday, January 21, 2004

The idea of “enterprise” backup software is that, once you venture into the realm of ‘ big databases' (hundreds of GB) or ‘really big databases’ (TB+) and you want 'quick recovery' you can't live with the relative slow loads of most DBMS systems (Oracle, Sybase, SQL Server, etc. are all slow to load from dumps).

So, you quiesce your DBMS system for a brief second, then have your management software take a snapshot (which can be virtually instantaneous if you pay for a big disk array). If your DBMS gets hosed, you push a button and the snapshot is immediately live – no waiting for your DBMS software to crunch through 240GB of tapes/disk files/etc. Or, if you have a PROD/DEV system and you want to refresh the dev system you can do so instantly with this software.

Of course, if you need quick recovery you’re far better off having a replication server / HA system set up so that a hardware failure will result in little or no downtime for your users.

MR
Wednesday, January 21, 2004