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).

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook