Introduction
"An ounce of prevention is worth a pound of cure". This rings true in the context of an enterprise application and a good backup regime is the prevention for data loss. This set of scripts gives you a quick and simple way of creating this backup regime for any given SQL database.
The scripts available for download are for Large and Regular databases and with and without notifications. I consider a Large database to be anything over 1gb but this may vary depending your own personal preferences and the rate of change that occurs within the database on a daily basis.
Outcomes
The scripts perform the following:
- Create a folder (in the specified location) to store the backup files.
- Create a sub folder for each day of the week, thus organising the backups.
- Create a series of SQL Server Agent jobs to perform the backups.
- A "standard" job will perform a transaction log backup at 6am, 12pm and 6pm daily.
- A "standard" job will perform a full database backup at 7pm daily.
- A "large" job will perform a transaction log backup hourly between 6am and 6pm daily.
- A "large" job will perform a full database backup at 7pm daily.
- A shrink and reorganise job will occur at 9am on Sunday.
- All daily transaction log backup files will be overwritten at the first occurence of the job for the day.
- All full database backup files will be overwritten each day.
Using the code
The code is relatively straight forward and there is only a small amount of information required. Set the values of the variables.
Without notification: Both regular and large scripts ask for the same information. Set the @Database and @Path and then execute the script.
use master
declare @Database nvarchar(50)
declare @Path nvarchar(255)
set @Database = '[Database]'
set @Path = ''
With notification: Both regular and large scripts ask for the same information. Set the @Database and @Path variables and the nominate the @OperatorName, @OperatorEmail and @OperatorNetSend variables. and then execute the script.
use master
declare @Database nvarchar(50)
declare @Path nvarchar(255)
declare @OperatorName nvarchar(255)
declare @OperatorEmail nvarchar(255)
declare @OperatorNetSend nvarchar(50)
set @Database = '[DataBase]'
set @Path = ''
set @OperatorName = '[Insert Name Here]'
set @OperatorEmail = '[Email Address]'
========================
Large.sql
/*
-------------------------------------------------------------------------------
----------------------------- LEGAL INFORMATION -------------------------------
-------------------------------------------------------------------------------
Copyright ｩ 2002 Edward Steward. All rights reserved. The contents of
this script in its entirety are copyright Edward Steward. The contents
of the code contained within this procedure, module, application, website,
script or any other container, be it known or unknown, is protected by copyright
laws of Australia.
Definition: Code includes but is not limited to VBScript, Javascript, HTML,
T-SQL, Visual Basic, Visual C++, C# (C-Sharp) and any other forms of computer
programming instruction, be it known or unknown. The code that this notice and
all other notices refers to, may be server or client side code, and may operate
within the shell or container including but not limited to an interperator,
compiler, container or other form of processing unit, be it known or unknown.
You are hereby granted permission to use this script but may not distribute,
alter, sell, copy this script without explicit written permission from
Edward Steward.
Failure to comply with the above may result in prosecution.
By continuing using this script or otherwise, you hereby agree to the terms of
this agreement. If you do not agree with the above terms you are required by
law to cease use of this script and delete it.
-------------------------------------------------------------------------------
-------------------------------- INSTRUCTIONS ---------------------------------
-------------------------------------------------------------------------------
The use of this script is for backup, shrink and optimisation schedules without
the need for programming.
*** ONLY USE THIS SCRIPT FOR LARGE DATABASES ***
If a database is of regular size (less than 1gb) then use the REGULAR.SQL script
This script will install the following:
Full backups for every day of the week @ 7:00pm (19:00)
Weekday transaction log backups w/ overwrite @ 6:00am (06:00)
Weekday, hourly transaction log backups w/ append @ 7:00am (07:00) through 6:00pm (18:00)
Database shrink and reorganise on Sunday @ 9:00am (09:00)
This script differs slightly due to the nature of larger databases and the need to
keep log files in good working order.
To use this script effectively, you will need to know the database and the file
location you wish to save the backups. Once you know this information, alter the
variables below, labelled @Database and @Path (e.g. set @Database = 'xxxxx')
With the variables set, run this script.
*/
use master
declare @Database nvarchar(50)
declare @Path nvarchar(255)
set @Database = '[Database]'
set @Path = '[Backup Path]'
-------------------------------------------------------------------------------
-------------------- DO NOT EDIT BEYOND THIS POINT ----------------------------
-------------------------------------------------------------------------------
declare @CommandLine nvarchar(255)
set @CommandLine = 'md "' + @Path + '"'
exec xp_cmdshell @CommandLine, no_output
declare @JobID BINARY(16)
set @JobID = null
declare @ReturnCode int
select @ReturnCode = 0
declare @BackupPath nvarchar(255)
declare @BackupFile nvarchar(255)
declare @BackupDesc nvarchar(255)
declare @DayName varchar(3)
declare @DayFullName varchar(10)
declare @Interval int
set @Interval = 2
declare @i int
set @i = 0
set nocount on
while @i < 7
begin
if @i = 0
begin
set @DayName = 'Sun'
set @DayFullName = 'Sunday'
set @Interval = 1
end
if @i = 1
begin
set @DayName = 'Mon'
set @DayFullName = 'Monday'
set @Interval = 2
end
if @i = 2
begin
set @DayName = 'Tue'
set @DayFullName = 'Tuesday'
set @Interval = 4
end
if @i = 3
begin
set @DayName = 'Wed'
set @DayFullName = 'Wednesday'
set @Interval = 8
end
if @i = 4
begin
set @DayName = 'Thu'
set @DayFullName = 'Thursday'
set @Interval = 16
end
if @i = 5
begin
set @DayName = 'Fri'
set @DayFullName = 'Friday'
set @Interval = 32
end
if @i = 6
begin
set @DayName = 'Sat'
set @DayFullName = 'Saturday'
set @Interval = 64
end
-------------------------------------------------------------------------------
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/' + @Database + '.bak'
set @BackupDesc = @Database + ' - Backup Full (' + @DayName + ')'
set @CommandLine = 'md "' + @BackupPath + '"'
exec xp_cmdshell @CommandLine, no_output
-- Delete the [Full Backup] job if it exists
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
-- Add the job steps
set @CommandLine = 'BACKUP DATABASE [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH INIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 190000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete the [Morning Transaction Log backup] job if it exists
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/TransLog.bak'
set @BackupDesc = @Database + ' - TransLog Morning (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
-- Add the job steps
set @CommandLine = 'BACKUP LOG [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH INIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 60000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete the [Day Repeat Transaction Log backup] job if it exists
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/TransLog.bak'
set @BackupDesc = @Database + ' - TransLog Mid (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
-- Add the job steps
set @CommandLine = 'BACKUP LOG [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH NOINIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 70000,
@freq_interval = @Interval,
@freq_subday_type = 8,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 180000
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete evening trans log backup (from Regular) if exists
set @BackupDesc = @Database + ' - TransLog Evening (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
set @i = @i + 1
END
-------------------------------------------------------------------------------
-- Delete the job with the same name (if it exists)
set @BackupDesc = @Database + ' - Shrink'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
set @JobID = NULL
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = NULL
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output ,
@job_name = @BackupDesc,
@owner_login_name = N'',
@description = N'',
@category_name = N'',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
-- Add the job steps
set @CommandLine = 'DBCC SHRINKDATABASE (N''' + @Database + ''', 0)'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
@start_step_id = 1
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 90000,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
@server_name = N'(local)'
print 'Added: ' + @BackupDesc
set nocount on
============================
LargeNotification.sql
/*
-------------------------------------------------------------------------------
----------------------------- LEGAL INFORMATION -------------------------------
-------------------------------------------------------------------------------
Copyright ｩ 2002 Edward Steward. All rights reserved. The contents of
this script in its entirety are copyright Edward Steward. The contents
of the code contained within this procedure, module, application, website,
script or any other container, be it known or unknown, is protected by copyright
laws of Australia.
Definition: Code includes but is not limited to VBScript, Javascript, HTML,
T-SQL, Visual Basic, Visual C++, C# (C-Sharp) and any other forms of computer
programming instruction, be it known or unknown. The code that this notice and
all other notices refers to, may be server or client side code, and may operate
within the shell or container including but not limited to an interperator,
compiler, container or other form of processing unit, be it known or unknown.
You are hereby granted permission to use this script but may not distribute,
alter, sell, copy this script without explicit written permission from
Edward Steward.
Failure to comply with the above may result in prosecution.
By continuing using this script or otherwise, you hereby agree to the terms of
this agreement. If you do not agree with the above terms you are required by
law to cease use of this script and delete it.
-------------------------------------------------------------------------------
-------------------------------- INSTRUCTIONS ---------------------------------
-------------------------------------------------------------------------------
The use of this script is for backup, shrink and optimisation schedules without
the need for programming.
*** ONLY USE THIS SCRIPT FOR LARGE databases ***
If a database is of regular size (less than 1gig) then use the REGULAR.SQL script
This script will install the following:
Full backups for every day of the week @ 7:00pm (19:00)
Weekday transaction log backups w/ overwrite @ 6:00am (06:00)
Weekday, hourly transaction log backups w/ append @ 7:00am (07:00) through 6:00pm (18:00)
Database shrink and reorganise on Sunday @ 9:00am (09:00)
This script differs slightly due to the nature of larger databases and the need to
keep log files in good working order.
To use this script effectively, you will need to know the database and the file
location you wish to save the backups. Once you know this information, alter the
variables below, labelled @Database and @Path (e.g. set @Database = 'xxxxx')
With the variables set, run this script.
*/
use master
declare @Database nvarchar(50)
declare @Path nvarchar(255)
declare @OperatorName nvarchar(255)
declare @OperatorEmail nvarchar(255)
declare @OperatorNetSend nvarchar(50)
set @Database = '[DataBase]'
set @Path = '[Backup Path]'
set @OperatorName = '[Insert Name Here]'
set @OperatorEmail = '[Email Address]'
set @OperatorNetSend = '[Net Send Address]'
-------------------------------------------------------------------------------
-------------------- DO NOT EDIT BEYOND THIS POINT ----------------------------
-------------------------------------------------------------------------------
if (exists (select [name] from msdb.dbo.sysoperators where [name] = @OperatorName))
---- Delete operator with the same name.
execute msdb.dbo.sp_delete_operator @name = @OperatorName
begin
execute msdb.dbo.sp_add_operator @name = @OperatorName, @enabled = 1, @email_address = @OperatorEmail, @netsend_address = @OperatorNetSend, @category_name = N'[Uncategorized]', @weekday_pager_start_time = 80000, @weekday_pager_end_time = 180000, @saturday_pager_start_time = 80000, @saturday_pager_end_time = 180000, @sunday_pager_start_time = 80000, @sunday_pager_end_time = 180000, @pager_days = 62
END
declare @CommandLine nvarchar(255)
set @CommandLine = 'md "' + @Path + '"'
exec xp_cmdshell @CommandLine, no_output
declare @JobID BINARY(16)
set @JobID = null
declare @ReturnCode int
select @ReturnCode = 0
declare @BackupPath nvarchar(255)
declare @BackupFile nvarchar(255)
declare @BackupDesc nvarchar(255)
declare @DayName varchar(3)
declare @DayFullName varchar(10)
declare @Interval int
set @Interval = 2
declare @i int
set @i = 0
set nocount on
while @i < 7
begin
if @i = 0
begin
set @DayName = 'Sun'
set @DayFullName = 'Sunday'
set @Interval = 1
end
if @i = 1
begin
set @DayName = 'Mon'
set @DayFullName = 'Monday'
set @Interval = 2
end
if @i = 2
begin
set @DayName = 'Tue'
set @DayFullName = 'Tuesday'
set @Interval = 4
end
if @i = 3
begin
set @DayName = 'Wed'
set @DayFullName = 'Wednesday'
set @Interval = 8
end
if @i = 4
begin
set @DayName = 'Thu'
set @DayFullName = 'Thursday'
set @Interval = 16
end
if @i = 5
begin
set @DayName = 'Fri'
set @DayFullName = 'Friday'
set @Interval = 32
end
if @i = 6
begin
set @DayName = 'Sat'
set @DayFullName = 'Saturday'
set @Interval = 64
end
-------------------------------------------------------------------------------
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/' + @Database + '.bak'
set @BackupDesc = @Database + ' - Backup Full (' + @DayName + ')'
set @CommandLine = 'md "' + @BackupPath + '"'
exec xp_cmdshell @CommandLine, no_output
-- Delete the [Full Backup] job if it exists
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 3,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0,
@notify_email_operator_name = @OperatorName
-- Add the job steps
set @CommandLine = 'BACKUP DATABASE [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH INIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 190000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete the [Morning Transaction Log backup] job if it exists
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/TransLog.bak'
set @BackupDesc = @Database + ' - TransLog Morning (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 2,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0,
@notify_email_operator_name = @OperatorName
-- Add the job steps
set @CommandLine = 'BACKUP LOG [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH INIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 60000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete the [Day Repeat Transaction Log backup] job if it exists
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/TransLog.bak'
set @BackupDesc = @Database + ' - TransLog Mid (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 2,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0,
@notify_email_operator_name = @OperatorName
-- Add the job steps
set @CommandLine = 'BACKUP LOG [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH NOINIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 70000,
@freq_interval = @Interval,
@freq_subday_type = 8,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 180000
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete evening trans log backup (from Regular) if exists
set @BackupDesc = @Database + ' - TransLog Evening (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
set @i = @i + 1
END
-------------------------------------------------------------------------------
-- Delete the job with the same name (if it exists)
set @BackupDesc = @Database + ' - Shrink'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
set @JobID = NULL
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = NULL
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output ,
@job_name = @BackupDesc,
@owner_login_name = N'',
@description = N'',
@category_name = N'',
@enabled = 1,
@notify_level_email = 3,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0,
@notify_email_operator_name = @OperatorName
-- Add the job steps
set @CommandLine = 'DBCC SHRINKDATABASE (N''' + @Database + ''', 0)'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
@start_step_id = 1
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 90000,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
@server_name = N'(local)'
print 'Added: ' + @BackupDesc
set nocount on
=====================
Regular.sql
/*
-------------------------------------------------------------------------------
----------------------------- LEGAL INFORMATION -------------------------------
-------------------------------------------------------------------------------
Copyright ｩ 2002 Edward Steward. All rights reserved. The contents of
this script in its entirety are copyright Edward Steward. The contents
of the code contained within this procedure, module, application, website,
script or any other container, be it known or unknown, is protected by copyright
laws of Australia.
Definition: Code includes but is not limited to VBScript, Javascript, HTML,
T-SQL, Visual Basic, Visual C++, C# (C-Sharp) and any other forms of computer
programming instruction, be it known or unknown. The code that this notice and
all other notices refers to, may be server or client side code, and may operate
within the shell or container including but not limited to an interperator,
compiler, container or other form of processing unit, be it known or unknown.
You are hereby granted permission to use this script but may not distribute,
alter, sell, copy this script without explicit written permission from
Edward Steward.
Failure to comply with the above may result in prosecution.
By continuing using this script or otherwise, you hereby agree to the terms of
this agreement. If you do not agree with the above terms you are required by
law to cease use of this script and delete it.
-------------------------------------------------------------------------------
-------------------------------- INSTRUCTIONS ---------------------------------
-------------------------------------------------------------------------------
The use of this script is for backup, shrink and optimisation schedules without
the need for programming.
*** ONLY USE THIS SCRIPT FOR REGULAR databases ***
If a database becomes quite large (1gig or more) then use the LARGE.SQL script
This script will install the following:
Full backups for every day of the week @ 7:00pm (19:00)
Weekday transaction log backups w/ overwrite @ 6:00am (06:00)
Weekday transaction log backups w/ append @ 12:00pm (12:00)
Weekday transaction log backups w/ append @ 6:00pm (18:00)
Database shrink and reorganise on Sunday @ 9:00am (09:00)
To use this script effectively, you will need to know the database and the file
location you wish to save the backups. Once you know this information, alter the
variables below, labelled @Database and @Path (e.g. set @Database = 'xxxxx')
With the variables set, run this script.
*/
use master
declare @Database nvarchar(50)
declare @Path nvarchar(255)
set @Database = '[Database]'
set @Path = '[Backup Path]'
-------------------------------------------------------------------------------
-------------------- DO NOT EDIT BEYOND THIS POINT ----------------------------
-------------------------------------------------------------------------------
declare @CommandLine nvarchar(255)
set @CommandLine = 'md "' + @Path + '"'
exec xp_cmdshell @CommandLine, no_output
declare @JobID BINARY(16)
set @JobID = null
declare @ReturnCode int
select @ReturnCode = 0
declare @BackupPath nvarchar(255)
declare @BackupFile nvarchar(255)
declare @BackupDesc nvarchar(255)
declare @DayName varchar(3)
declare @DayFullName varchar(10)
declare @Interval int
set @Interval = 2
declare @i int
set @i = 0
set nocount on
while @i < 7
begin
if @i = 0
begin
set @DayName = 'Sun'
set @DayFullName = 'Sunday'
set @Interval = 1
end
if @i = 1
begin
set @DayName = 'Mon'
set @DayFullName = 'Monday'
set @Interval = 2
end
if @i = 2
begin
set @DayName = 'Tue'
set @DayFullName = 'Tuesday'
set @Interval = 4
end
if @i = 3
begin
set @DayName = 'Wed'
set @DayFullName = 'Wednesday'
set @Interval = 8
end
if @i = 4
begin
set @DayName = 'Thu'
set @DayFullName = 'Thursday'
set @Interval = 16
end
if @i = 5
begin
set @DayName = 'Fri'
set @DayFullName = 'Friday'
set @Interval = 32
end
if @i = 6
begin
set @DayName = 'Sat'
set @DayFullName = 'Saturday'
set @Interval = 64
end
-------------------------------------------------------------------------------
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/' + @Database + '.bak'
set @BackupDesc = @Database + ' - Backup Full (' + @DayName + ')'
set @CommandLine = 'md "' + @BackupPath + '"'
exec xp_cmdshell @CommandLine, no_output
-- Delete the [Full Backup] job if it exists
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
-- Add the job steps
set @CommandLine = 'BACKUP DATABASE [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH INIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 190000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete the [Morning Transaction Log backup] job if it exists
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/TransLog.bak'
set @BackupDesc = @Database + ' - TransLog Morning (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
-- Add the job steps
set @CommandLine = 'BACKUP LOG [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH INIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 60000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete the [Mid Transaction Log backup] job if it exists
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/TransLog.bak'
set @BackupDesc = @Database + ' - TransLog Mid (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
-- Add the job steps
set @CommandLine = 'BACKUP LOG [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH NOINIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 120000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete the [Evening Transaction Log backup] job if it exists
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/TransLog.bak'
set @BackupDesc = @Database + ' - TransLog Evening (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
-- Add the job steps
set @CommandLine = 'BACKUP LOG [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH NOINIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 180000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
set @i = @i + 1
END
-------------------------------------------------------------------------------
-- Delete the job with the same name (if it exists)
set @BackupDesc = @Database + ' - Shrink'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
set @JobID = NULL
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = NULL
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output ,
@job_name = @BackupDesc,
@owner_login_name = N'',
@description = N'',
@category_name = N'',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
-- Add the job steps
set @CommandLine = 'DBCC SHRINKDATABASE (N''' + @Database + ''', 0)'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
@start_step_id = 1
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 90000,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
@server_name = N'(local)'
print 'Added: ' + @BackupDesc
set nocount on
==========================
/*
-------------------------------------------------------------------------------
----------------------------- LEGAL INFORMATION -------------------------------
-------------------------------------------------------------------------------
Copyright ｩ 2002 Edward Steward. All rights reserved. The contents of
this script in its entirety are copyright Edward Steward. The contents
of the code contained within this procedure, module, application, website,
script or any other container, be it known or unknown, is protected by copyright
laws of Australia.
Definition: Code includes but is not limited to VBScript, Javascript, HTML,
T-SQL, Visual Basic, Visual C++, C# (C-Sharp) and any other forms of computer
programming instruction, be it known or unknown. The code that this notice and
all other notices refers to, may be server or client side code, and may operate
within the shell or container including but not limited to an interperator,
compiler, container or other form of processing unit, be it known or unknown.
You are hereby granted permission to use this script but may not distribute,
alter, sell, copy this script without explicit written permission from
Edward Steward.
Failure to comply with the above may result in prosecution.
By continuing using this script or otherwise, you hereby agree to the terms of
this agreement. If you do not agree with the above terms you are required by
law to cease use of this script and delete it.
-------------------------------------------------------------------------------
-------------------------------- INSTRUCTIONS ---------------------------------
-------------------------------------------------------------------------------
The use of this script is for backup, shrink and optimisation schedules without
the need for programming.
*** ONLY USE THIS SCRIPT FOR REGULAR databases ***
If a database becomes quite large (1gig or more) then use the LARGE.SQL script
This script will install the following:
Full backups for every day of the week @ 7:00pm (19:00)
Weekday transaction log backups w/ overwrite @ 6:00am (06:00)
Weekday transaction log backups w/ append @ 12:00pm (12:00)
Weekday transaction log backups w/ append @ 6:00pm (18:00)
Database shrink and reorganise on Sunday @ 9:00am (09:00)
To use this script effectively, you will need to know the database and the file
location you wish to save the backups. Once you know this information, alter the
variables below, labelled @Database and @Path (e.g. set @Database = 'xxxxx')
With the variables set, run this script.
*/
use master
declare @Database nvarchar(50)
declare @Path nvarchar(255)
declare @OperatorName nvarchar(255)
declare @OperatorEmail nvarchar(255)
declare @OperatorNetSend nvarchar(50)
set @Database = '[DataBase]'
set @Path = '[Backup Path]'
set @OperatorName = '[Insert Name Here]'
set @OperatorEmail = '[Email Address]'
set @OperatorNetSend = '[Net Send Address]'
-------------------------------------------------------------------------------
-------------------- DO NOT EDIT BEYOND THIS POINT ----------------------------
-------------------------------------------------------------------------------
if (exists (select [name] from msdb.dbo.sysoperators where [name] = @OperatorName))
---- Delete operator with the same name.
execute msdb.dbo.sp_delete_operator @name = @OperatorName
begin
execute msdb.dbo.sp_add_operator @name = @OperatorName, @enabled = 1, @email_address = @OperatorEmail, @netsend_address = @OperatorNetSend, @category_name = N'[Uncategorized]', @weekday_pager_start_time = 80000, @weekday_pager_end_time = 180000, @saturday_pager_start_time = 80000, @saturday_pager_end_time = 180000, @sunday_pager_start_time = 80000, @sunday_pager_end_time = 180000, @pager_days = 62
END
declare @CommandLine nvarchar(255)
set @CommandLine = 'md "' + @Path + '"'
exec xp_cmdshell @CommandLine, no_output
declare @JobID BINARY(16)
set @JobID = null
declare @ReturnCode int
select @ReturnCode = 0
declare @BackupPath nvarchar(255)
declare @BackupFile nvarchar(255)
declare @BackupDesc nvarchar(255)
declare @DayName varchar(3)
declare @DayFullName varchar(10)
declare @Interval int
set @Interval = 2
declare @i int
set @i = 0
set nocount on
while @i < 7
begin
if @i = 0
begin
set @DayName = 'Sun'
set @DayFullName = 'Sunday'
set @Interval = 1
end
if @i = 1
begin
set @DayName = 'Mon'
set @DayFullName = 'Monday'
set @Interval = 2
end
if @i = 2
begin
set @DayName = 'Tue'
set @DayFullName = 'Tuesday'
set @Interval = 4
end
if @i = 3
begin
set @DayName = 'Wed'
set @DayFullName = 'Wednesday'
set @Interval = 8
end
if @i = 4
begin
set @DayName = 'Thu'
set @DayFullName = 'Thursday'
set @Interval = 16
end
if @i = 5
begin
set @DayName = 'Fri'
set @DayFullName = 'Friday'
set @Interval = 32
end
if @i = 6
begin
set @DayName = 'Sat'
set @DayFullName = 'Saturday'
set @Interval = 64
end
-------------------------------------------------------------------------------
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/' + @Database + '.bak'
set @BackupDesc = @Database + ' - Backup Full (' + @DayName + ')'
set @CommandLine = 'md "' + @BackupPath + '"'
exec xp_cmdshell @CommandLine, no_output
-- Delete the [Full Backup] job if it exists
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 3,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0,
@notify_email_operator_name = @OperatorName
-- Add the job steps
set @CommandLine = 'BACKUP DATABASE [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH INIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 190000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete the [Morning Transaction Log backup] job if it exists
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/TransLog.bak'
set @BackupDesc = @Database + ' - TransLog Morning (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 2,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0,
@notify_email_operator_name = @OperatorName
-- Add the job steps
set @CommandLine = 'BACKUP LOG [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH INIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 60000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete the [Mid Transaction Log backup] job if it exists
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/TransLog.bak'
set @BackupDesc = @Database + ' - TransLog Mid (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 2,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0,
@notify_email_operator_name = @OperatorName
-- Add the job steps
set @CommandLine = 'BACKUP LOG [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH NOINIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 120000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
-- Delete the [Evening Transaction Log backup] job if it exists
set @BackupPath = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/'
set @BackupFile = @Path + '/' + @Database + '/Backup/0' + cast((@i + 1) as varchar(1)) + ' ' + @DayName + '/TransLog.bak'
set @BackupDesc = @Database + ' - TransLog Evening (' + @DayName + ')'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = null
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = @BackupDesc,
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 2,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0,
@notify_email_operator_name = @OperatorName
-- Add the job steps
set @CommandLine = 'BACKUP LOG [' + @DataBase + '] TO DISK = N''' + @BackupFile + ''' WITH NOINIT , NOUNLOAD , NAME = N''' + @BackupDesc + ''', NOSKIP , STATS = 10, NOFORMAT '
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 180000,
@freq_interval = @Interval,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
print 'Added: ' + @BackupDesc
-------------------------------------------------------------------------------
set @i = @i + 1
END
-------------------------------------------------------------------------------
-- Delete the job with the same name (if it exists)
set @BackupDesc = @Database + ' - Shrink'
select @JobID = [job_id] from msdb.dbo.sysjobs where ([name] = @BackupDesc)
if (@JobID is not null)
begin
execute msdb.dbo.sp_delete_job @job_name = @BackupDesc
set @JobID = NULL
print 'Deleted: ' + @BackupDesc
end
-- Add the job
set @JobID = NULL
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output ,
@job_name = @BackupDesc,
@owner_login_name = N'',
@description = N'',
@category_name = N'',
@enabled = 1,
@notify_level_email = 3,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0,
@notify_email_operator_name = @OperatorName
-- Add the job steps
set @CommandLine = 'DBCC SHRINKDATABASE (N''' + @Database + ''', 0)'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @CommandLine,
@database_name = @Database,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
@start_step_id = 1
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = @BackupDesc,
@enabled = 1,
@freq_type = 8,
@active_start_date = 20021130,
@active_start_time = 90000,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
@server_name = N'(local)'
print 'Added: ' + @BackupDesc
set nocount on
set @OperatorNetSend = '[Net Send Address]'
The @Operator... variables provide SQL Server with the information required to set the notifications.
The @OperatorEmail allows for an email address, however, this will require SQLMail to be configured in the SQL Server Agent.
The @OperatorNetSend allows SQL Server to perform a net
send to a computer however with increased security these days, the
Messenger service is typically disabled and this may not function.
Points of Interest
You will require access to the Maste database and elevated permissions.
Using the Query Analyzer will certainly make things easy and will also allow you to parse the script before execution.
I've been using the above regimes since 2001 with much success. The only downside is the total size of the backup set especially when dealing with large (> 1gb) databases.
扫一扫
345
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
