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:
- Automatizovaná záloha všech databází na SQL Express
by Michal Altair Valášek - Backup Log with Truncate_Only
by Brent Ozar - Comprehensive Backup Script
by Simon Facer - Configuring SQL Server Agent Operators
by Taylor Gerring - Database Backups: Things You Need To Do Now
by The Scary DBA - Princip práce SQL Server Recovery Models
by Marek Chmel - SQL Backup and Restore Bundle
by redgate - SQL safe backup
by idera - SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt
by Pinal Dave - SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database
by Pinal Dave - SQL SERVER – 2008 – Introduction to New Feature of Backup Compression
by Pinal Dave - SQL SERVER – A Quick Script for Point in Time Recovery – Back Up and Restore
by Pinal Dave - SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model
by Pinal Dave - SQL SERVER – Effect of Compressed Backup Setting at Server Level on Database Backup
by Pinal Dave - SQL SERVER – Retrieve and Explore Database Backup without Restoring Database – Idera virtual database
by Pinal Dave - SQL SERVER – Shrinking Truncate Log File – Log Full
by Pinal Dave - truncate_only’ is not a recognized BACKUP option
by MSDN - Using Database Mail in SQL Server 2008
by Jeff Hunter
Placko's database KB 