Creating an automated database backup by means of SQL Server Agent Job

Posted: January 15, 2012 in MSSQL
Tags:

Task: to create a fully automatic database backup system by means of SQL Server Agent Job with an e-mail fails notification subsystem



EXEC [master].[dbo].[sp_addumpdevice]
     @devtype      = N'disk',
     @logicalname  = N'AdventureWorks2008R2',
     @physicalname = N'D:\SqlData\MSSQL\Backup\AdventureWorks2008R2.bak'
GO

Source code 1: SSMS -> Object Explorer -> [sql_server_instance] -> Server Objects -> Backup Devices -> AdventureWorks2008R2



EXEC [master].[dbo].[sp_configure] 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC [master].[dbo].[sp_configure] 'xp_cmdshell', 1
GO
RECONFIGURE
GO

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Backup]
AS
BEGIN
    DECLARE @Out INT
    
    -- NOTE: make sure the database is in Full Recovery Model and is online!
    ALTER DATABASE [AdventureWorks2008R2]
    SET RECOVERY FULL
    ALTER DATABASE [AdventureWorks2008R2]
    SET ONLINE
    -- NOTE: functional for a FileStream database as well!
    
    DBCC CHECKDB([AdventureWorks2008R2])
    
    -- Create a database backup
    EXEC @Out = [master]..[xp_cmdshell]
                N'D:\SqlData\MSSQL\Backup\BACKUP_FILO_BEGIN.bat'
    
    CHECKPOINT
    
    BACKUP LOG [AdventureWorks2008R2]
               TO DISK='NUL' --WITH TRUNCATE_ONLY
    BACKUP DATABASE [AdventureWorks2008R2]
                    TO [AdventureWorks2008R2] WITH COMPRESSION --WITH INIT
    
    -- Copy the backup database to a tape
    EXEC @Out = [master]..[xp_cmdshell]
                N'D:\SqlData\MSSQL\Backup\BACKUP_FILO_END.bat'
END
GO

Source code 2: SSMS -> Object Explorer -> [sql_server_instance] -> Databases -> System Databases -> master -> Programmability -> Stored Procedures -> dbo.Backup



REM ***Creating a Database Backup***

REM Path: "D:\SqlData\MSSQL\Backup\"
SET FILEPATH="D:\SqlData\MSSQL\Backup\"

SET FILENAME="AdventureWorks2008R2"

IF EXIST %FILEPATH%\%FILENAME%.BA4 DEL    %FILEPATH%\%FILENAME%.BA4
IF EXIST %FILEPATH%\%FILENAME%.BA3 RENAME %FILEPATH%\%FILENAME%.BA3 %FILENAME%.BA4
IF EXIST %FILEPATH%\%FILENAME%.BA2 RENAME %FILEPATH%\%FILENAME%.BA2 %FILENAME%.BA3
IF EXIST %FILEPATH%\%FILENAME%.BA1 RENAME %FILEPATH%\%FILENAME%.BA1 %FILENAME%.BA2
IF EXIST %FILEPATH%\%FILENAME%.BAK COPY   %FILEPATH%\%FILENAME%.BAK %FILEPATH%\%FILENAME%.BA1

SET FILENAME=
SET FILEPATH=

Source code 3: Creating a database backup – BACKUP_FILO_BEGIN.bat



REM ***Copying the Database Backup to a Tape***

REM Path: "D:\SqlData\MSSQL\Backup\"
SET FILEPATHSRC="D:\SqlData\MSSQL\Backup\"

REM Path: "\\[server_name]\SQL_BAK$"
SET FILEPATHDST="\\[server_name]\SQL_BAK$"

SET FILENAME="AdventureWorks2008R2"

COPY %FILEPATHSRC%\%FILENAME%.BAK %FILEPATHDST%\%FILENAME%.BAK

SET FILENAME=
SET FILEPATHSRC=
SET FILEPATHDST=

Source code 4: Copying the database backup to a tape – BACKUP_FILO_END.bat



USE [msdb]
GO

EXEC [msdb].[dbo].[sp_add_operator]
     @name                      = N'BACKUP',
     @enabled                   = 1,
     @weekday_pager_start_time  = 90000,
     @weekday_pager_end_time    = 180000,
     @saturday_pager_start_time = 90000,
     @saturday_pager_end_time   = 180000,
     @sunday_pager_start_time   = 90000,
     @sunday_pager_end_time     = 180000,
     @pager_days                = 0,
     @email_address             = N'test@example.eu',
     @category_name             = N'[Uncategorized]'
GO

Source code 5: SSMS -> Object Explorer -> [sql_server_instance] -> SQL Server Agent -> Operators -> BACKUP



USE [msdb]
GO

BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT  @ReturnCode = 0
    
    IF NOT EXISTS
    (
        SELECT [name]
        FROM   [msdb].[dbo].[syscategories]
        WHERE  [name]           = N'Database Maintenance'
               AND
               [category_class] = 1 -- Job
    )
    BEGIN
        EXEC @ReturnCode = [msdb].[dbo].[sp_add_category]
                           @class = N'JOB',
                           @type  = N'LOCAL',
                           @name  = N'Database Maintenance'
        IF (@@ERROR != 0 OR @ReturnCode != 0)
        BEGIN
            GOTO QuitWithRollback
        END
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_job]
		       @job_name                   = N'BACKUP',
		       @enabled                    = 1,
		       @notify_level_eventlog      = 2,
		       @notify_level_email         = 2,
		       @notify_level_netsend       = 0,
		       @notify_level_page          = 0,
		       @delete_level               = 0,
		       @description                = N'Creating a database backup by means of SQL Server Agent Job',
		       @category_name              = N'Database Maintenance',
		       @owner_login_name           = N'NB0046\Marian Placko',
                       @notify_email_operator_name = N'BACKUP',
                       @job_id                     = @jobId OUTPUT
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobstep]
		       @job_id               = @jobId,
		       @step_name            = N'BACKUP',
		       @step_id              = 1,
		       @cmdexec_success_code = 0,
		       @on_success_action    = 1,
		       @on_success_step_id   = 0,
		       @on_fail_action       = 2,
		       @on_fail_step_id      = 0,
		       @retry_attempts       = 0,
		       @retry_interval       = 0,
		       @os_run_priority      = 0,
		       @subsystem            = N'TSQL',
		       @command              = N'EXEC [dbo].[Backup]',
		       @database_name        = N'master',
		       @flags                = 0
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    EXEC @ReturnCode = [msdb].[dbo].[sp_update_job]
                       @job_id        = @jobId,
                       @start_step_id = 1
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    DECLARE @guid UNIQUEIDENTIFIER
    SET     @guid = NEWID()
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobschedule]
		       @job_id                 = @jobId,
		       @name                   = N'BACKUP',
		       @enabled                = 1,
		       @freq_type              = 8,
		       @freq_interval          = 62,
		       @freq_subday_type       = 1,
		       @freq_subday_interval   = 0,
		       @freq_relative_interval = 0,
		       @freq_recurrence_factor = 1,
		       @active_start_date      = 20111230,
		       @active_end_date        = 99991231,
		       @active_start_time      = 200000,
		       @active_end_time        = 235959,
		       @schedule_uid           = @guid
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobserver]
                       @job_id      = @jobId,
                       @server_name = N'(local)'
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
COMMIT TRANSACTION
    GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRANSACTION
    END
EndSave:
GO

Source code 6: SSMS -> Object Explorer -> [sql_server_instance] -> SQL Server Agent -> Jobs -> BACKUP



-- Enable a Database Mail
[master].[dbo].[sp_configure] 'show advanced options', 1
GO
RECONFIGURE
GO
[master].[dbo].[sp_configure] 'Database Mail XPs', 1
GO
RECONFIGURE
GO

-- Set up an Account
 -- Port number: 25
 -- SMPT authentication: anonymous
EXECUTE [msdb].[dbo].[sysmail_add_account_sp]
        @account_name    = 'BACKUP',
        @description     = 'Creating a database backup by means of SQL Server Agent Job',
        @email_address   = 'test@example.eu',
        @display_name    = 'BACKUP',
        @replyto_address = 'test@example.eu',
        @mailserver_name = 'localhost' -- NOTE: SMTP server must be set up!
GO

-- Set up a Profile
EXECUTE [msdb].[dbo].[sysmail_add_profile_sp]
        @profile_name = 'BACKUP',
        @description  = 'Creating a database backup by means of SQL Server Agent Job'
GO

-- Associate the Profile with the Account
EXECUTE [msdb].[dbo].[sysmail_add_profileaccount_sp]
        @profile_name    = 'BACKUP',
        @account_name    = 'BACKUP',
        @sequence_number = 1
GO

-- Add NB0046\Marian Placko to the Database Mail Role
EXEC [msdb].[dbo].[sp_addrolemember]
     @rolename   = 'DatabaseMailUserRole',
     @membername = 'NB0046\Marian Placko'
GO

-- Grant access to the Profile to NB0046\Marian Placko
EXEC [msdb].[dbo].[sysmail_add_principalprofile_sp]
     @profile_name   = 'BACKUP',
     @principal_name = 'NB0046\Marian Placko',
     @is_default     = 0
GO

-- Send a test e-mail
EXEC [msdb].[dbo].[sp_send_dbmail]
     @profile_name='BACKUP',
     @recipients='test@example.eu',
     @subject='Test message',
     @body='This is the body of the test message.'

Source code 7: SSMS -> Object Explorer -> [sql_server_instance] -> Management -> Database Mail -> BACKUP



-- Enable SQL Server Agent to use the Database Mail
 -- NOTE: make sure to start SQL Server Agent!
 EXEC master.dbo.xp_ServiceControl 'QUERYSTATE','SQLServerAgent'
 -- Current Service State: Running.
 --EXEC master.dbo.xp_ServiceControl 'START', 'SQLServerAgent'
 GO

EXEC [msdb].[dbo].[sp_set_sqlagent_properties]
     @email_save_in_sent_folder = 1

EXEC [master].[dbo].[xp_instance_regwrite]
     N'HKEY_LOCAL_MACHINE',
     N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
     N'UseDatabaseMail',
     N'REG_DWORD',
     1

EXEC [master].[dbo].[xp_instance_regwrite]
     N'HKEY_LOCAL_MACHINE',
     N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
     N'DatabaseMailProfile',
     N'REG_SZ',
     N'BACKUP'
GO

-- Enable SQL Server Agent to set Fail-safe Operator
EXEC [master].[dbo].[sp_MSsetalertinfo]
     @failsafeoperator   = N'BACKUP',
     @notificationmethod = 1 -- E-mail
GO
 -- NOTE: make sure to restart SQL Server Agent!
 EXEC master.dbo.xp_ServiceControl 'STOP', 'SQLServerAgent'
 GO
 -- Message: Service Stopped.
 EXEC master.dbo.xp_ServiceControl 'START', 'SQLServerAgent'
 GO
 -- Message: Service Started.

Source code 8: SSMS -> Object Explorer -> [sql_server_instance] -> SQL Server Agent -> Properties -> Alert System


Additional references:
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s