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
No comments:
Post a Comment