Take the 2-minute tour ×
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.

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?

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

1 Answer 1

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
add comment

Your Answer

 
discard

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.