Standard Backup Commands

SQL Server 2005 and SQL Server 2000 have two commands that essentially perform the same action—DUMP and BACKUP (as in DUMP DATABASE or BACKUP DATABASE, and DUMP LOG or BACKUP LOG). The DUMP command is a holdover from SQL Server 6.5 and earlier, when a database backup was simply a copy of the database as it looked just before the backup operation started. No user changes that were made during the dump operation were saved to the backup file.

Starting with version 7, SQL Server produces a true “dynamic” backup, in which user changes that occur during the backup operation are captured in the transaction log and saved to the backup file. The backup is a picture of the database at the time the backup finishes. The DUMP command is still recognized for backward compatibility, but Microsoft doesn’t recommend using it for new development. At some point Microsoft will stop supporting DUMP and you’ll have to modify application code that still uses this command.

If you’ve always been diligent about backing up your SQL Server databases and you’re only interested in learning what’s new in SQL Server 2005, here’s a reason to continue paying attention to your backups: SQL Server 2005 doesn’t include the standard DBCC DBREPAIR. The “replacement” for this command is actually DROP DATABASE. (Ouch!)