Backup Database Stored Procedure

The following script will handle Full backups, Differential backups, and transaction log backups.

/*   ____  _____ _____ 
    |    \|     |     |
    |  |  |   --| | | |
    |____/|_____|_|_|_|
      Author: RJ Hatch
 Create date: 2013-12-26
 Description: Backup Databases
  Parameters:
    @DatabaseName   - Name of the database
    @BackupType     - F=full, D=differential, L=log
    @BackupLocation - backup directory location
*/ 
CREATE PROCEDURE [dbo].[sp_BackupDatabase]  
    @DatabaseName NVARCHAR(100),
    @BackupType CHAR(1),
    @BackupLocation NVARCHAR(200) 
AS
BEGIN
    SET NOCOUNT ON; 
           
    -- Declare variables
    DECLARE @BackupName NVARCHAR(100),
            @BackupFile NVARCHAR(100),
            @SqlCommand NVARCHAR(1000),
            @Date NVARCHAR(8),
            @Time NVARCHAR(6)

    -- Provide the backup a name for storing in the media
    SELECT  @Date = REPLACE(CONVERT(VARCHAR, GETDATE(), 102), '.', ''), --YYYYMMDD
            @Time = REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', ''), --HHMMSS
            @BackupFile = @backupLocation + REPLACE(REPLACE(@DatabaseName, '[', ''), ']', '') +
                CASE
                    WHEN @BackupType='F' THEN '_FULL_' + @Date + '_' + @Time
                    WHEN @BackupType='D' THEN '_DIFF_' + @Date + '_' + @Time
                    ELSE '_LOG_' + @Date + '_' + @Time
                END + 
                CASE
                    WHEN @BackupType='F' THEN '.BAK'
                    WHEN @BackupType='D' THEN '.BAK'
                    ELSE '.TRN'
                END, --[c:\directory\][Database]_[LongType]_[Date][_Time].[Ext]
            @BackupName = REPLACE(REPLACE(@DatabaseName, '[', ''), ']', '') + ' ' +
                CASE
                    WHEN @BackupType='F' THEN 'full'
                    WHEN @BackupType='D' THEN 'differential'
                    ELSE 'log'
                END + ' backup for ' + @Date + '_' + @Time, -- [Database] [BackupType] backup for [Date]_[Time]
            @SQLCommand = 'BACKUP ' +
                CASE
                    WHEN @BackupType='F' THEN 'DATABASE'
                    WHEN @BackupType='D' THEN 'DATABASE'
                    ELSE 'LOG'
                END + ' ' +
                @DatabaseName + ' TO DISK = ''' + @BackupFile + ''' WITH ' +
                CASE
                    WHEN @BackupType='D' THEN 'DIFFERENTIAL, '
                    ELSE ''
                END + 'INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'

    -- Execute the generated SQL command
    EXEC(@sqlCommand)
END

Run it as the following

--Full Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'F', 'c:\temp\'

--Differential Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'D', 'c:\temp\'

--Transaction Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'L', 'c:\temp\'
Profile photo of superadmin

RJ writes custom Windows and Web applications using the Microsoft .NET framework, and enjoys solving problems with code, often while listening to a techno soundcloud stream. He hopes to one day write an application that many businesses will use.

Twitter 

Comments are closed.