Customized backup scripts(Arvinder & Srivathsani)
Created By:
Srivathsani M(Srivathsani_m@infosys.com)
Arvinder Singh Khosla(arvinder_khosla@infosys.com)
Details:
The scripts gives the flexibility to create customized backups.
1)It gives the provision to give the backup files a customized name with customized time stamp.
2)It allows the user to create the backup directory.
3)These scripts can be used in the form of stored procedures by passing the variables defined in the script as parameters.
--/****************The script for the Full backup of Database**********************/--##STEP 1 --> Declaration of Variables
declare @dirPath varchar(60)
declare @fileName varchar(60)
declare @destFilePath varchar(60)
declare @timeStamp varchar(30)
--##STEP 2--> Assigning values to variables
select @dirPath = 'H:\CorpDW\Backups\CorpDWProd' --The path where the backups have to be stored
select @timeStamp = convert(char(8),getdate(),112) + '_' + replace(convert(char(8),getdate(),8),':','_') --Convert the time stamp according to the user's needs
select @fileName = 'CorpDW_Full_Backup_' + @timeStamp --Customized File Name
select @destFilePath = @dirPath + '\' + @filename + '.bak' --The full customized destination path
--##STEP 3--> Creating the subdirectory if it doesn't exist
EXECUTE master.dbo.xp_create_subdir @dirPath
--##STEP 4--> Full BackUp
BACKUP DATABASE [CorpDWProd] TO
DISK = @destFilePath WITH NOFORMAT, NOINIT,NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 10
--/*********************************************************************************/
/*****************Script For Differential Back Up of Database****************/--##STEP 1 --> Declaration of Variables
declare @dirPath varchar(60)
declare @fileName varchar(60)
declare @destFilePath varchar(60)
declare @timeStamp varchar(30)
--##STEP 2--> Assigning values to variables
select @dirPath = 'H:\CorpDW\Backups\CorpDWProd'
select @timeStamp = convert(char(8),getdate(),112) + '_' + replace(convert(char(8),getdate(),8),':','_')
select @fileName = 'CorpDW_Diff_Backup_' + @timeStamp
select @destFilePath = @dirPath + '\' + @filename + '.bak'
--##STEP 3--> Creating the subdirectory if it doesn't exist
EXECUTE master.dbo.xp_create_subdir @dirPath
--##STEP 4--> Differential BackUp
BACKUP DATABASE [CorpDWProd]
TO DISK = @destFilePath WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = @fileName , SKIP, REWIND, NOUNLOAD, STATS = 10
--/*********************************************************************************/
/*****************Script For Transactional Back Up of Corp DW ****************/--##STEP 1 --> Declaration of Variables
declare @dirPath varchar(60)
declare @fileName varchar(60)
declare @destFilePath varchar(60)
declare @timeStamp varchar(30)
--##STEP 2--> Assigning values to variables
select @dirPath = 'H:\CorpDW\Backups\CorpDWProd'
select @timeStamp = convert(char(8),getdate(),112) + '_' + replace(convert(char(8),getdate(),8),':','_')
select @fileName = 'CorpDW_Txn_Backup_' + @timeStamp
select @destFilePath = @dirPath + '\' + @filename + '.trn'
--##STEP 3--> Creating the subdirectory if it doesn't exist
EXECUTE master.dbo.xp_create_subdir @dirPath
--##STEP 4--> Transaction log BackUp
BACKUP LOG [CorpDWProd]
TO DISK = @destFilePath WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 10
--/*********************************************************************************/
/****************The script for the Full backup of System Databases**********************/--##STEP 1 --> Declaration of Variables
declare @dirPath varchar(60)
declare @fileName varchar(60)
declare @destFilePath varchar(60)
declare @timeStamp varchar(30)
declare @dbname varchar(20)
declare @subdir varchar(60)
select @dirPath = 'H:\CorpDW\Backups'
select @timeStamp = convert(char(8),getdate(),112) + '_' + replace(convert(char(8),getdate(),8),':','_')
--##STEP 2--> Assigning values to variables
select @dbname = 'master'
select @fileName = 'Master_Full_Backup_' + @timeStamp
select @destFilePath = @dirPath + '\' + @dbname + '\' + @filename + '.bak'
--##STEP 3--> Creating the subdirectory if it doesn't exist
select @subdir = @dirPath + '\' + @dbname
EXECUTE master.dbo.xp_create_subdir @subdir
--##STEP 4--> Full BackUp of Master
BACKUP DATABASE [master] TO
DISK = @destFilePath WITH NOFORMAT, NOINIT,NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 10
--##STEP 5--> Assigning values to variables for MSDB backup
select @dbname = 'msdb'
select @fileName = 'Msdb_Full_Backup_' + @timeStamp
select @destFilePath = @dirPath + '\' + @dbname + '\' + @filename + '.bak'
--##STEP 6--> Creating the subdirectory if it doesn't exist
select @subdir = @dirPath + '\' + @dbname
EXECUTE master.dbo.xp_create_subdir @subdir
--##STEP 7 --> Full BackUp Of MSDB
BACKUP DATABASE [msdb] TO
DISK = @destFilePath WITH NOFORMAT, NOINIT,NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 10
--##STEP 8--> Assigning values to variables for model backup
select @dbname = 'msdb'
select @fileName = 'Model_Full_Backup_' + @timeStamp
select @destFilePath = @dirPath + '\' + @dbname + '\' + @filename + '.bak'
--##STEP 9--> Creating the subdirectory if it doesn't exist
select @subdir = @dirPath + '\' + @dbname
EXECUTE master.dbo.xp_create_subdir @subdir
--##STEP 10 --> Full BackUp Of Model
BACKUP DATABASE [model] TO
DISK = @destFilePath WITH NOFORMAT, NOINIT,NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 10
--/*********************************************************************************/
