How to do a Full and Differential Database Backup in SQL Server

Regular Backup's of your database is very important! A lot of people know how to do a Full and Differential backup using the SQL Server Management Studio (SSMS). It is as simple as opening SSMS > Right click Database > Tasks > BackUp. However not many are familiar doing the same operation using T-SQL.

Here's how to use T-SQL to do a Full and Differential Backup of your database in SQL Server 2005/2008

Note: In order to do a differential backup, a backup of the database needs to exists first since a differential backup copies all the data and log info that have changed since the last backup. So let us do a Full backup first

-- Full database backup needs to exist

-- before a Differential backup is taken

BACKUP DATABASE Northwind

   TO DISK = 'C:\MyBackUp\NW_Mar20_09.bak'

   WITH DESCRIPTION = 'First BackUp Of NW',

   INIT

GO

INIT parameter overwrites existing backups preserving the media header.
DESCRIPTION is for keeping notes about the backup

Note: The Folder 'MyBackUp' should exist before you

and now the Differential backup

-- Create a differential db backup

-- appending the backup to the full backup

BACKUP DATABASE Northwind

   TO DISK = 'C:\MyBackUp\NW_Mar20_09.diff'

   WITH DIFFERENTIAL,

   NOINIT,

   STATS= 50

GO

STATS gives additional info about the progress during a backup. A sample is shown below in bold:

66 percent processed.
Processed 48 pages for database 'Northwind', file 'Northwind' on file 3.
100 percent processed.
Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 3.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 49 pages in 0.095 seconds (3.957 MB/sec).