How to SQL Server database AutoBackup and Zip or .Rar (.bak) File and Transfer using FTP?

You have to follow these three steps to back up your SQL Server databases by using Windows Task Scheduler:

Step A: Use SQL Server Management Studio Express or Sqlcmd to create the following stored procedure in your master database:

USE [master] 

GO 

/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

-- ============================================= 

-- Author: Microsoft 

-- Create date: 2010-02-06

-- Description: Backup Databases for SQLExpress

-- Parameter1: databaseName 

-- Parameter2: backupType F=full, D=differential, L=log

-- Parameter3: backup file location

-- =============================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases]  

            @databaseName sysname = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

       SET NOCOUNT ON; 

           

            DECLARE @DBs TABLE

            (

                  ID int IDENTITY PRIMARY KEY,

                  DBNAME nvarchar(500)

            )

           

             -- Pick out only databases which are online in case ALL databases are chosen to be backed up

             -- If specific database is chosen to be backed up only pick that out from @DBs

            INSERT INTO @DBs (DBNAME)

            SELECT Name FROM master.sys.databases

            where state=0

            AND name=@DatabaseName

            OR @DatabaseName IS NULL

            ORDER BY Name

           

            -- Filter out databases which do not need to backed up

            IF @backupType='F'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')

                  END

            ELSE IF @backupType='D'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE IF @backupType='L'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE

                  BEGIN

                  RETURN

                  END

           

            -- Declare variables

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000) 

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                  

                       

            -- Loop through the databases one by one

            SELECT @Loop = min(ID) FROM @DBs

      WHILE @Loop IS NOT NULL

      BEGIN

-- Database Names have to be in [dbname] format since some have - or _ in their name

      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format

      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

-- Create backup filename in path\filename.extension format for full,diff and log backups

      IF @backupType = 'F'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'D'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'L'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media

      IF @backupType = 'F'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime

      IF @backupType = 'D'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime

      IF @backupType = 'L'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

       IF @backupType = 'F' 

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'

                  END

       IF @backupType = 'D'

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

       IF @backupType = 'L' 

                  BEGIN

               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

-- Execute the generated SQL command

       EXEC(@sqlCommand)

-- Goto the next database

SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END

Step B: In a text editor, create a batch file that is named Sqlbackup.bat, and then copy the text from one of the 

following examples into that file, depending on your scenario:

Example1: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

 sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases
 @backupLocation='D:\SQLBackups\', @backupType='F'" 

 Example2: Differential backups of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password

// Sqlbackup.bat

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases  @backupLocation ='D:\SQLBackups', @BackupType=’D’"

 Note: The SQLLogin shouldhave at least the Backup Operator role in SQL Server.

Example 3: Log backups of all databases in local named instance of SQLEXPRESS by using Windows Authentication

 // Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"

Example 4: Full backups of the database USERDB in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

 sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"

Similarly, you can make a differential backup of USERDB by pasting in 'D' for the @backupType parameter and a log backup of USERDB by pasting in 'L' for the @backupType parameter.

Step c: Set .Rar or .zip File Name as Current Date


for /F "tokens=1-4 delims=/ " %%A in ('date/t') do (
set DateDay=%%C
set DateMonth=%%B
set DateYear=%%D
)

set CurrentDate=%DateDay%-%DateMonth%-%DateYear%

step D: Create .Rar File


set path="C:\Program Files (x86)\WinRAR\";%path%

winrar.exe a -ep1 C:\backup\%CurrentDate%.rar C:\backup\


Step E:Transfer .Rar File using FTP


echo user Username>> ftpcmd.dat
echo Password>> ftpcmd.dat
echo cd backup_Folder>> ftpcmd.dat
echo put "C:\backup\*.rar">> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat  255.255.255.255


Example:Full Script


DEL /F /S /Q /A "C:\backup\*.bak"
DEL /F /S /Q /A "C:\backup\*.rar"

sqlcmd -S  Server\SQLEXPRESS -U Sa -P server -Q "EXEC sp_BackupDatabases @backupLocation='C:\backup\', @backupType='F'"

@REM ------- BEGIN xpi.bat ----------------
@setlocal
@echo off


for /F "tokens=1-4 delims=/ " %%A in ('date/t') do (
set DateDay=%%C
set DateMonth=%%B
set DateYear=%%D
)

set CurrentDate=%DateDay%-%DateMonth%-%DateYear%

set path="C:\Program Files (x86)\WinRAR\";%path%

winrar.exe a -ep1 C:\backup\%CurrentDate%.rar C:\backup\

echo user Username>> ftpcmd.dat
echo Password>> ftpcmd.dat
echo cd backup_Folder>> ftpcmd.dat
echo put "C:\backup\*.rar">> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat  255.255.255.255


REM ------- END xpi.bat ------------------