Backup SQL Server database using T-SQL

We are in thought of only DBA’s can take backup using T-SQL, But anyone can take backup and restore backup very easily with the help of T-SQL. we can backup an entire database, transaction log, or one or more file or filegroups with the help of T-SQL.

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

Before taking differential backup, a full backup of a database should be taken. Because the differential backup takes backup of data an log files since the last full database backup. so let we discuss Full database backup first,

Full Database Backup:

   1: BACKUP DATABASE AdventureWorks

   2:     TO DISK = 'C:\DBBackups\AdventureWorks_2009_03_07.bak'

   3:     WITH DESCRIPTION = 'New AdventureWorks Backup',

   4:     INIT

   5: GO

   6:  

   7: -- DBBackups folder should be exists before taking backup

Description parameter is used to store notes about the backup and INIT parameter overwrites existing backups preserving the media header.

Differential Database Backup:

   1: BACKUP DATABASE AdventureWorks

   2:     TO DISK = 'C:\DBBackups\AdventureWorks_2009_03_07_1.diff'

   3:     WITH DIFFERENTIAL,

   4:     NOINIT,

   5:     STATS = 50    

   6: GO

STATS gives a additional information about the backup progress.

Related Posts

1. Backup SQL Server Database using SSMS
2. Split Backup Files while Backup and Restore SQL Server Database
3. SQL Server 2005 database DBA Checklist for database administrator
4. Backup and Restore Transaction Log in SQL Server
5. BACKUP Compression in SQL Server 2008

Tags: Backup using T-SQL, SQL Server, SQL Server Backup, SQL Server Database Backup, T-SQL, T-SQL Backup