1.
Background
This article describes and helps the users to
take different types of SQL database backups using T-SQL script.
take different types of SQL database backups using T-SQL script.
2.
Objective of the Script
Followings are the
objectives of the script:
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.
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
@DATABASENAME –
Database name that needs to be backup
·
@BACKUPTYPE – Types of Backups (Full, Differential
@BACKUPTYPE – Types of Backups (Full, Differential
or Transaction log)
·
@BACKUPFILEPATH –
Location where backuped file will be stored
@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
Name and Backup file destination
DECLARE @DATABASENAME NVARCHAR(200)
DECLARE @BACKUPFILEPATH NVARCHAR(200)
–Setting value for variable
SET @DATABASENAME = ‘BI_Reporting’
SET @BACKUPFILEPATH = ‘C:DATABI_Reporting.bak’
— Backup Database
BACKUP DATABASE @DATABASENAME TO
DISK =
@BACKUPFILEPATH
DISK =
@BACKUPFILEPATH
- Differential Backup:
We are using WITH DIFFERENTIAL keyword.
–Declare variable for Database
Name and Backup file destination
Name and Backup file destination
DECLARE @DATABASENAME NVARCHAR(200)
DECLARE @BACKUPFILEPATH NVARCHAR(200)
–Setting value for variable
SET @DATABASENAME = ‘BI_Reporting’
SET @BACKUPFILEPATH = ‘C:DATABI_Reporting.dif’
— Backup Database
BACKUP DATABASE @DATABASENAME
TO DISK = @BACKUPFILEPATH WITH
DIFFERENTIAL
TO DISK = @BACKUPFILEPATH WITH
DIFFERENTIAL
- Transaction log: We are using
BACKUP LOG keyword.
–Declare variable for Database
Name and Backup file destination
Name and Backup file destination
DECLARE @DATABASENAME NVARCHAR(200)
DECLARE @BACKUPFILEPATH NVARCHAR(200)
–Setting value for variable
SET @DATABASENAME = ‘BI_Reporting’
SET @BACKUPFILEPATH = ‘C:DATABI_Reporting.trn’
— Backup Database
BACKUP LOG @DATABASENAME TO DISK = @BACKUPFILEPATH