Monday, March 30, 2009

How to take a full and differential backup using T-SQL

Regularly backup’s of your database is very important. A lot of people know how to take a backup using SQL Server Management Studio (SSMS).

Here is the T-SQL command which shows how to take a full and differential database backup in SQL Server 2005/2008.

Note: In order to do a differential backup, full backup of the database needs to exist first since a differential backup copies all the data and log info that have been changed since the last backup.

Full backup:

-- Full database backup needs to exist

 -- before a Differential backup is taken

 BACKUP DATABASE AdventureWork

    TO DISK = 'C:\BackUpFolder\AdventureWork_Full_20090230.bak'

    WITH DESCRIPTION = 'First BackUp Of Adventure Work Database',

    INIT

 GO

·         INIT parameter overwrites existing backups preserving the media header.

·         DESCRIPTION is for keeping notes about the backup.

Note: 'BackUpFolder' should exist before you executing it

Differential backup:

-- Create a differential db backup

 -- appending the backup to the full backup

 BACKUP DATABASE Northwind

    TO DISK = 'C:\ BackUpFolder\ AdventureWork_Diff_20090230.diff'

    WITH DIFFERENTIAL,

    NOINIT,

    STATS= 50

 GO

 ·         STATS gives additional info about the progress during a backup.

  

No comments:

Post a Comment