You should back up a database immediately after you create it. After that, you should continue to back it up on a regular schedule.
Databases and transaction logs are backed up onto dump devices. A dump device can be a disk file or a tape drive (or for compatibility with earlier releases a diskette). You can back up a database to one device or multiple devices. You can back up multiple databases or transaction logs to a common device or separate devices.
You should maintain regular backups of all databases, including:
If you store the transaction log on a different disk device from its database, you should back the transaction log up more frequently to record changes made since the last database backup. For example, you might back up a heavily used database once a week but back up its transaction log daily (thus capturing changes made since the last database dump). For information about backing up the transaction log, see About Transaction Log Dumps, later in this chapter.
In case of media failure, you can recover your databases if you have been making regular backups (dumps) of your databases and their transaction logs. This depends completely on the regular use of database and transaction log dumps.
Backup responsibility is usually assigned to either the SA or database owner. However, permission to dump a database or a transaction log can be transferred by the database owner to other users. In any case, the user responsible should set up a regular backup schedule. (SQL Enterprise Manager provides a scheduling interface that allows you to automate this.)
There is no best schedule for backing up databases and transaction logs, but the frequency of your backups determines the largest amount of work that could be lost if a media failure should occur. For an installation with large and active databases, daily backups of the transaction log and weekly backups of the database are typical.
You can back up a database or a transaction log while the database is active. This type of backup is known as a dynamic dump. The dynamic dump makes backups convenient and continuous operation possible. However, because the dynamic dump slows SQL Server somewhat, it is best to execute it when the database is not being heavily updated.
The DUMP DATABASE statement executes a checkpoint internally. This forces all completed transactions to be written out. It then makes an exact image of the database, capturing it as it was when the DUMP statement was executed.
However, transactions that aren't committed at the start of the dump don't appear in the database should it be subsequently loaded. Any changes made after the dump begins are not reflected in the image. Only pages that are used are backed up. This includes both data and log pages.
Note The files created by a backup can only be used by a load. To copy data for use by applications other than SQL Server, use bcp, SQL Transfer Manager, or a similar utility, not the DUMP statement.
While the dump is in progress, a change to a data or index page that has already been backed up takes place immediately and does not change the dumped image. If an update is requested on a page that has not yet been backed up, the system immediately dumps that page and then makes the change. In other words, when users write a page during a dump, they must wait for the dump to write the page first.
Note It is highly recommended that you always run DBCC CHECKDB, DBCC CHECKALLOC or NEWALLOC, and DBCC CHECKCATALOG on a database either just before or just after dumping it. For information see Before Backing Up, earlier in this chapter.
Each server is installed with a specified character set and sort order. When you dump a database, the backup has the same character set and sort order as the server. You cannot load a database with one character set and sort order onto a server that has a different character set and sort order. For more information about character sets and sort orders, see Microsoft SQL Server Setup. For information about changing a character set or sort order, see Chapter 3, Configuring Servers.