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 Database.
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 shown below.
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 Overwrite Media.
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' WITH FORMAT;
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 manage them.