Components of a SQL Server Database
Before exploring each of the various options in backing up a SQL Server database, let's review how information is stored in one.
A SQL Server database stores data in one or more database files. While many databases only use a single file to store data, there are good reasons sometimes to split your data among several files. As an example, if you need the extra performance, you can store tables that are frequently used in their own separate files. By placing those files on separate disks, you avoid contention for disk access. Or your database might be too large to fit on one disk volume of your server and so must use more than one filea separate file on each disk volume. If many files are being used for the database, the files themselves are sometimes organized into separate file groups, with each group consisting of several individual files.
Each SQL Server database must also have at least one file for the transaction log, which stores all changes that are made to the database. If you are unfamiliar with the use of the SQL Server transaction log, please see my previous 10-Minute Solution, "Using SQL Server Recovery Models," for a more in-depth description.
Fully Backing Up the Whole Database
Regardless of how many files your database uses, you can create a complete backup of your database with one simple command:
BACKUP DATABASE [dbname] to [backup_device]
BACKUP DATABASE pubs to disk = 'c:\mssql\backup\pubs.bak'
creates a complete backup of the pubs database using the file c:\mssql\backup\pubs.bak.
The database can remain online and accessible to users while this backup is being made. In order to take a consistent snapshot of the database, a copy of the transaction log is also included with the database backup.
In the backup file, SQL Server stores the names and locations of the files actually used in the database. Upon restoring the database, SQL Server recreates all the necessary files, as many as there might be. A database thus restored is equivalent to the point in time that the backup finished.
A complete database backup is very simple to execute and use in recovery. If your data doesn't change often, you might simply schedule a nightly full backup of your database. Even if you need more frequent backups, regularly scheduling a full database backup each hour might be sufficient (if your database is small enough).