Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

    @BaseLocation   varchar(1024),
    @BackupType     varchar(32)


    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) <> '\'
        Set @BaseLocation = @BaseLocation + '\'
    Set @FullPath = @BaseLocation + @FileName
    Print @FullPath

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


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?

share|improve this question
Why not use an existing solution instead? Ola Hallengren's script is popular. – Mark Storey-Smith Mar 21 '13 at 9:51
up vote 3 down vote accepted

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.
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.