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


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

   WITH DESCRIPTION = 'First BackUp Of NW',



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


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



   STATS= 50


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


Thomas LACH said...
This comment has been removed by the author.
Thomas LACH said...

Is it possible to select the Full Backup to use when you to a Differential Backup ?