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

2 comments:

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 ?