Backup stored procedure for full, differential and log backup

Question

I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure:

Alter PROCEDURE BackupDB
(   
    @BaseLocation   varchar(1024),
    @BackupType     varchar(32)

)
AS
BEGIN

    Declare @DBName     varchar(255) = db_name()

    Declare @FileName   varchar(256)

    Declare @Date datetime = getdate()
    Set @BackupType = UPPER(@BackupType)

    Set @FileName = @DBName + '_'  + Cast(Year(@Date) as varchar(4)) + '_' + 
                    Right('00' + Cast(Month(@Date) as varchar(2)),2) + '_' +
                    Right('00' + Cast(Day(@Date) as varchar(2)),2) + '_' + 
                    Right('00' + Cast(DatePart(hh,@Date) as varchar(2)),2) + '_' + 
                    Right('00' + Cast(DatePart(mi,@Date) as varchar(2)),2) + '_' +
                    Right('00' + Cast(DatePart(ss,@Date) as varchar(2)),2) + '_' + 
                    Right('000' + Cast(DatePart(ms,@Date) as varchar(3)),3) + '_' + UPPER(@BackupType) +                    
                    (Case When @BackupType = 'FULL' Or @BackupType = 'DIFFERENTIAL' Then '.BAK' Else '.TRN' End)

    Declare @FullPath varchar(1280) = ''

    If Right(@BaseLocation,1) <> '\'
    Begin
        Set @BaseLocation = @BaseLocation + '\'
    End
    Set @FullPath = @BaseLocation + @FileName
    Print @FullPath


    If @BackupType = 'FULL'
    Begin
        Backup database @DBName To Disk = @FullPath
    End
    Else If @BackupType = 'DIFFERENTIAL'
    Begin
        Backup database @DBName To Disk = @FullPath WITH DIFFERENTIAL
    End
    Else If @BackupType = 'LOG'
    Begin
        BACKUP LOG @DBName
             TO Disk = @FullPath
    End

END
GO

I ran my stored procedure and it seems to work perfectly for parameters passed in. Now the next step is to create a SQL JOB for this. However before I do this, I want to know is this correct procedure? I've read many articles and they all seem to point out one line "Test backup strategy."

Now I would be creating a SQL JOB weekly, daily and every 5 minutes i.e Weekly full backups, Daily Differential and Log backups every 5 minutes.

Do you think this stored procedure is incorrectly written or do you think it should be all fine for my scenario?

Answer 1

There are quite a few solutions to have your daily backups (full, diff and logs). You could:

• Write your own script: it might be useful if you need a fine control on it, or if you need to respect some naming convention. I did not test it, but your code looks reasonnable.
• Use the maintenance plans: very easy to set up, but less control. This might be a good solution in your case.
• Use a 3rd party script / tool: Ola Hallengren's scripts are very good, but you can find plenty of others.