Home / server / Questions / 855
Accepted
Swinders
Asked: 2009-05-01 06:15:44 +0800 CST

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

  • 772

Currently we backup our MSSQL 2000 and 2005 databases using software to copy files to tape nightly. Database sizes are 14-16Gb and 500Mb.

As SQL server can backup a database to a file would we be better scheduling SQL server to create a backup file and then backing up these files to tape? Also if we use this method is it possible to somehow create a log of transactions that have been completed since the last backup so we can recreate the database and apply the additional transactions?

sql-server backup

6 Answers

  1. 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

    • 2
  2. Best Answer

    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

    • 2

  3. Do you mean you currently back up the database MDF and LDF files to tape? I think this can be OK, as long as the database is offline while you do it (i.e. database detached or SQL Service shut down), but if you try and do that while the database is in use you will probably end up with backups that dont work because the files will have changed while being copied.

    Creating a full SQL Backup and putting that onto tape is much more common in my experience.

    Also, to answer your second question, SQL Server does support Transaction Log backups. The MSDN documentation on the Full Recovery Model would be a good place to start reading up.

    • 1

  4. As you've seen by the answers, most folks do backup to disk first, then to tape. What I've often seen (and recommended) is to backup immediately to disk that is on the server itself (this could be SAN attached storage, the key is the backup isn't written across the network). Once this immediate backup is done, it is then copied to a central backup server. You keep an immediate copy of what you need to restore locally. On that backup server you keep several days worth of backups. That way if you do have to rollback to a previous day or two, you're not requesting a tape. And of course, you back up that central backup server to tape. So that covers your ability to recover.

    With respect to the types of backup you should be doing, if you want to recover all transactions and be able to do what we call point in time recovery, you need to make sure the databases have their recovery model set to full recovery and you're going to want to do Full backups along with transaction log backups. You may want to intersperse differential backups to reduce the amount of files you are restoring. All of this is covered in books on-line and it'll give a better description than any of us can in a forum post.

    As far as 3rd party products like LiteSpeed and Red Gate's SQL Backup, they used to be faster than native backup. That's because they use an API that SQL Server didn't. This was true in SQL Server 2000, but I'm not sure if it is still the case. However, they do encryption and compression and so may be well worth consideration given the size of your DBs.

    • 1

  5. Scripted backup to disk is easy and operates very quickly:

    osql -S [ip_of_server] -Q " BACKUP DATABASE [database_name] TO DISK = 'C:\Backups\Backup.bak' "

    Doesn't work to back up to different location over the network (not that this is a good idea anyway) with a UNC path. It's easier anyway to make backups locally and store them encrypted.

    Hard disk is cheap, and increasingly reliable. We got rid of our tapes a while back and have never looked back.

    • 1

  6. I like these instructions better: http://www.sqldbatips.com/showarticle.asp?ID=27

    • 1