Microsoft SQL Server 2008 is still one of the most popular corporate database engines, and it has a lot of backup types supported. Let’s briefly remember all of these backup types and find out how to perform the most useful ones. After reading this post, you will know how to backup a database in SQL Server 2008 using query language or SQL Server Management Studio GUI.
Table of Contents
Backup Types Overview
Microsoft SQL Server has five backup types supported: full and differential, transaction- and tail-logs backup, and copy backup. But the last two ones are quite specific, so we will shortlist three of them:
- Full backup contains all database data on the moment the backup has finished, so it doesn’t allow point-in-time restoration. After a successful full backup runs, transaction logs are truncated.
- SQL Server differential backup is based on the most recent full data backup and contains only the data changed since the last full run. It requires less time to perform the back up.
- Transaction logs (T-Logs) backup contains every transaction made in SQL Server since the last full or differential backup. Using log backups allows you to create a database backup every hour or even minute - that is important for core business databases.
Please remember that SQL Server 2008 backup and recovery scenarios available are restricted by the database recovery model selected: it controls transaction logs management. There are generally three recovery models: Simple, Full, and Bulk-Logged. If choosing to create a T-Logs backup, you should set the Full recovery model for the given database.
Now let’s take a look at how to perform such backup types.
How to Perform Full and Differential Backup of a Database in SQL Server 2008
Using Query
Easiest way to perform any backup in Microsoft SQL Server is using query language. You should right-click root view in the navigation tree of SQL Server Management Studio, and select New Query. Then paste a script and click Execute on the toolbar.
Here is the script to create a full database backup:
USE TestDB; GO BACKUP DATABASE TestDB TO full_backups WITH COMPRESSION, DESCRIPTION = 'Sample full backup', NAME = 'Set of FULL backups'; GO
You may notice full_backups as a backup target. It is a logical “backup device” representing a particular file on the HDD. You need to create one or more “devices” for any backup type described below. Use this command to create one:
BACKUP DATABASE TestDB TO diff_backups WITH DIFFERENTIAL; GO
Add DIFFERENTIAL as an option next to the WITH operator if need to create a SQL Server 2008 differential backup.
Using SQL Server Management Studio
If you prefer GUI, then open SQL Server Management studio, navigate to the target database and choose Tasks - Back Up… in the context menu:
In the Back Up database screen, choose Full as a backup type and click Add at the screen bottom to add a backup device:
If you need to create a SQL Server 2008 differential backup - use Differential in the same drop-down menu. We suggest to use separate backup devices for every backup type. It allows you to manage backup storage more efficiently and simplifies navigation over the set of files.
After the backup job is completed, you can find resulting data in the Media Contents section of the particular backup device’s context menu.
Note: you can also automate such tasks using the SQL Server Agent feature - it allows you to create a repetitive task using a backup job dialog or query script
To learn more about SQL Server backup automation, check out this article:
Further reading SQL Server Automatic Backup
Transaction Logs Backup in SQL Server 2008
Before creating the first T-Logs backup, please check if your target database uses the Full Recovery model. Otherwise, your backups can not be consistent since the SQL engine will keep only a few log files at once by automatically deleting the oldest ones.
Perform transaction logs backup using the query script below:
BACKUP LOG TestDB TO logs_backups; GO
You can achieve the same using GUI by selecting Transaction Log type in the drop-down menu of the backup dialog seen above:
Do not forget to add a separate backup device for the T-Logs backup type.
Summary
Microsoft SQL Server has a flexible backup model, allowing you to keep the balance of time spent to backup and speed of data recovery. In most cases you should create a full backup weekly with a set of daily differential backups. If your company needs a better RTO/RPO, consider using T-Logs backup as well.
However, if you don’t want to have to write scripts manually or learn T-SQL, check MSP360 Backup for MS SQL Server. MSP360 Backup allows you to schedule recurring backups of all the types described above to local or cloud storage of your choice, without requiring scripting or extensive customization.
Simple. Reliable.
