Schedule SQL Server Database Auto Backup

Step-1: Open Microsoft SQL Server Management Studio

Step-2: Go to “SQL Server Agent”

Step-3: Go to “Jobs” and add new jobs (suppose “DailyBackup”)

Step-4: Right Click and open Properties

Step-5: Go to Step tab and set the parameter 1. Step name: DailyBackup, 2. Type: “Transact-SQL script(T-SQL)”,  3. Database: master, and 4. Command: (Code is given bellow)

declare @DBString varchar(max),
@DelString varchar(400),
@FinalBackup varchar(max),
@dbnAME VARCHAR (max),
@MonthlyFolde varchar(500),
@DailyFolderName varchar(500),
@CreateFolder varchar(2000),
@CopyCommand varchar(500),
@destination varchar(200),
@DatabaseName varchar(50)
–exec sp_dbBackup
declare CursorName cursor for
SELECT name FROM sys.databases WHERE name NOT IN (‘master’, ‘tempdb’, ‘model’, ‘msdb’,’ReportServerTempDB’,’ReportServer’,’distribution’,’POS’,’KSInventory’,’AdmissionTest’)
open CursorName

Fetch NEXT FROM CursorName into @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
set @MonthlyFolde = DATENAME(MONTH,GETDATE()) + DATENAME(YEAR,GETDATE())
set @DailyFolderName = DATENAME(DAY,GETDATE()) + DATENAME(MONTH,GETDATE()) + DATENAME(YEAR,GETDATE())

set @CreateFolder = ‘IF exist D:\BackupToday\’ + @MonthlyFolde + ‘ (mkdir D:\BackupToday\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘) ELSE (MKDIR D:\BackupToday\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘)’
EXEC XP_CMDSHELL @CreateFolder

set @dbnAME = @DatabaseName + ‘_’ + CONVERT(varchar(12),GETDATE(),112) + ‘_’ + SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,1,2) + SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,4,2) +
SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,7,2)

set @DBString = ‘D:\BackupToday\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME
set @FinalBackup = @DBString + ‘.bak’
set @DelString = ‘DEL ‘ + @FinalBackup

BACKUP DATABASE @DatabaseName TO DISK = @FinalBackup
— COMPAC RAR
DECLARE @SERVER VARCHAR(100), @COMMAND VARCHAR(300), @FILE VARCHAR(100)
SET @SERVER = @@SERVERNAME
SET @COMMAND = ‘”D:\BackupToday\Rar” a D:\BackupToday\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME + ‘.rar D:\BackupToday\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME + ‘.bak’

–select @COMMAND
EXEC XP_CMDSHELL @COMMAND
—- EXCLUIR FILE BACKUP
EXEC XP_CMDSHELL @DelString

Fetch NEXT FROM CursorName into @DatabaseName
END
CLOSE CursorName
DEALLOCATE CursorName
Step-6: now Press ok

Step-7: Goto: Schedule tab- set frequency and daily frequency and duration

That’s it !!! Good Programming !!! Thank you all !!!!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s