SQL Database Backups using T-SQL

1.    Background

This article describes and helps the users to take different types of SQL database backups using T-SQL script.

2.    Objective of the Script

  Followings are the objectives of the script:

•  Backup of databases without using UI in SQL Server.
•  It provides full, differential and transaction log backups.
• This script can be used in some backup applications that requires backup of   databases with front end application.
• This script can be used with SQL 2000, SQL 2005 and SQL 2008.
•  This script can be utilized in many backup applications that required backups of databases without login into the servers.

3.    Understanding the script

In this script, we are using the “BACKUP” command for taking the different types of backups.

• Script Parameters: we are using three input parameters for this script:

·         @DATABASENAME                - Database name that needs to be backup

·         @BACKUPTYPE                       - Types of Backups (Full, Differential

                                                  or Transaction log)

·         @BACKUPFILEPATH               - Location where backuped file will be stored          

• Full Backup: For Full Backup, we are simply using BACKUP Command.

--Declare variable for Database Name and Backup file destination

DECLARE @DATABASENAME NVARCHAR(200)

DECLARE @BACKUPFILEPATH NVARCHAR(200)

--Setting value for variable

SET @DATABASENAME = 'BI_Reporting'

SET @BACKUPFILEPATH = 'C:\DATA\BI_Reporting.bak'

-- Backup Database

BACKUP DATABASE @DATABASENAME TO DISK = @BACKUPFILEPATH

• Differential Backup: We are using WITH DIFFERENTIAL keyword.

--Declare variable for Database Name and Backup file destination

DECLARE @DATABASENAME NVARCHAR(200)

DECLARE @BACKUPFILEPATH NVARCHAR(200)

--Setting value for variable

SET @DATABASENAME = 'BI_Reporting'

SET @BACKUPFILEPATH = 'C:\DATA\BI_Reporting.dif'

-- Backup Database

BACKUP DATABASE @DATABASENAME TO DISK = @BACKUPFILEPATH WITH DIFFERENTIAL

• Transaction log: We are using BACKUP LOG keyword.

--Declare variable for Database Name and Backup file destination

DECLARE @DATABASENAME NVARCHAR(200)

DECLARE @BACKUPFILEPATH NVARCHAR(200)

--Setting value for variable

SET @DATABASENAME = 'BI_Reporting'

SET @BACKUPFILEPATH = 'C:\DATA\BI_Reporting.trn'

-- Backup Database

BACKUP LOG @DATABASENAME TO DISK = @BACKUPFILEPATH