How can I backup an SQL server (2000 and 2005) database?

Solution 1:

There's really no reason NOT to use SQL Server's native backup functionality. It's great, understands transaction logs, and gives you most of the functionality you need. (Third party SQL Server Backup solutions are great because they work with the APIs that Microsoft has exposed, and usually provide encryption and compression support - and compression not only saves disk space, but typically reduces backup and restore times.)

So yeah... I'd recommend doing backups WITH SQL Server.

And make sure to do REGULAR transaction log backups. (I'd recommend doing them every 15 minutes or less - regular log file backups help keep your log file lean/mean 1 and for some reason end-users are always grumpy when the server crashes and they have to redo all of their work over the last x hours since the last logfile or full/differential backup.) Then, for redundancy purposes, use robocopy, syncback, file system replication or something to move copies of those backups to another location to protect against hardware/disk crashes or fires in the data center and so on.

Feel free to check out these two free videos for more info and ideas:

http://www.sqlservervideos.com/video/backup-options

http://www.sqlservervideos.com/video/sqlbackup-best-practices/

[1] [SQL Server Magazine: Maximing Storage Performance]3

Solution 2:

Note: My biggest bug bear with SQL Server (all editions) is transaction log backups.

We run a full backup our SQL db's nightly and transaction logs every 30 minutes, to disk (on another server, different SAN) to date stamped files (fairly simple SQL script and SSIS).

Our script also creates the "restore.sql" script to restore the last full backup and all the transaction logs up to that date.

We then zip up files over 2 days old, delete any over 30 days old (but keep month end backups in an archive). We replicate this drive off-site (we're luck we have 200 miles between large sites and large WAN connection between them.)

We then backup this to tape. (Belt, braces and more braces! Mostly for political reasons)

We run a lot of SQL Server databases and commercial tools are either too expensive or just don't give us the bang for the buck. But I would recommend RedGate SQLBackup for smaller installations