In this article, well examine backing up and restoring SQL
Server databases. In addition to covering standard backup options such as Full
Backups and Differential Backups, new SQL Server 2008 features such as Backup
Compression will be introduced. Backing up from the SQL Server Management
Studio as well as from the command line will be demonstrated. Well also cover
backing up the System databases in addition to user created databases. Scheduled
Backups will be reviewed. Security will be examined, including login problems
associated with moving a database from a test server to a production machine.
To begin, well start with a discussion of Full Backups.
Full Backups are the simplest to understand and provide a complete backup
solution. A Full Backup creates a file of the entire database, including Transaction
Logs. (In the next section, well discuss Transaction Logs and Recovery
Models.) When viewing a database from inside the SQL Server Management Studio
Object Explorer, there are several different types of objects, including
Tables, Views, Stored Procedures, and Security objects.
All of these are included in a Full SQL Server backup file.
To create a backup, right click the database, select Tasks, and then click
Backup. This will bring up the Back Up Database screen with the General
settings page in view. The Source will be filled in with the database you
selected. Ensure Full is selected for the Backup type.
The other backup options such as Differential and
Transaction Log will be covered later. In the Backup Component section, select
The other radio button option of Files and Filegroups is
used when the database was specifically created to span more than one file on
the operating system. This is sometimes done on very large databases to
increase performance. Even if this is the case, selecting Database will include
any and all Filegroups.
Next, give the backup a Name and Description.
The following section Backup set will expire can be used
when backing up to tape or creating groups of backups Media Sets. In this
example, well backup to the hard disk so leave the default of After 0 days.
The final option on this screen is for the location of the
backup. By default, SQL Server will place backups in the MSSQL\Backup folder.
By tradition, backups end with the file extension BAK. However, this is not a
requirement. Notice that more than one destination file can be specified as
This can allow SQL backups to be spread out over different
hard disks. In the above example, both files would be required to Restore.
This technique can also be used to span backups across multiple Tapes when the
media is too small to hold the entire database on a single tape.
To examine the remaining choices, select Options from the
upper right menu.
The first set of options is collectively labeled as
These are typically used when backing up to Tape. When
backing up to the hard drive, if you used a unique name for your backup on the
previous screen, then these options can be ignored. If the backup name was not
unique, select the Overwrite All existing backup sets radio button. This
will create a file with only this most recent backup contained inside. To
explain this fuller, a backup file can contain more than one actual backup copy.
You could keep appending and appending backups to the same file. When you went
to restore from this file, you would be prompted to choose which specific
backup set inside to restore. There is not a speed improvement, disk savings,
or any other performance gain by combining multiple backups into one file.
In the next section, Reliability, select Verify backup
when finished to ensure our backup is valid.
The Transaction Log section will be grayed out because the
Full Backup will automatically truncate the log file, more on this later when
Logs and Recovery topics are covered.
The final section contains options for Compression (SQL
Server 2008 Only). Compression is a new feature in SQL Server 2008. The
compression used is a proprietary Microsoft formula that may shrink the
physical size of the backup down to 20% of the original. Testing with the
Adventure Works light database reduced the backup file size from 5,400 KB down
to 1,100 KB. If compression is used, the Restore will automatically recognize
this so no additional configuration options are required. One potential
downside to compression is that it will place an added CPU burden on the
computer processor during the backup.
Click the OK button in the lower right hand corner to start
the backup. Once completed, a message saying Backup Complete will appear.
Backups can also be performed from the command line or in a
script. To execute a backup using TSQL, open a new Query Window. (To open a
Query Window, from inside the Management Studio, click the New Query button in
the upper right corner.) The following statement executes a Full backup to
the hard drive:
BACKUP DATABASE AdventureWorksLT2008
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\test.bak'
The backup statement starts by specifying the database to be
backed up. Unfortunately there isnt a way to say backup all databases.
They must be individually specified. However, later well review a simple
script and a task to include all databases. The next statement specifies that
well backup to disk and the destination of the file. If we were backing up to
a tape drive, use To TAPE instead of To Disk. Lastly, With Format means
create a new file. Without this last statement, if the backup file already
existed, our new backup would append to it.
There are many options and different types of backups for
SQL Server, but a Full Backup will include all the objects belonging to a
database. In the next article, well begin Restoring databases, as well as examining
Transaction Logs, Recovery Methods, and the various Backup options of how to
See All Articles by Columnist Don Schlichting