February 12, 2008 at 2:53 am
I have been trying to work out a very simple way to automate a back procedure for all my servers.
I am trying to create a job, whereby there is a daily full back of all databases on the servers.
The backups will be backed up on to another drive, in which each of the db's to be backed will create a subdirectory and and place the .bak file in the folder.
e.g a database called test, willl be back up on to D:\test\test.bak.
the current one keeps on failing, so am trying to recreate it, can anyone help?
February 12, 2008 at 4:04 am
You can do a maintenance plan wich includes backup
Regards Ramon
Regards Ramon
February 12, 2008 at 4:12 am
I agree with you Ramon, but the problem with the maintainance plan is that it wont enable me to encrypt the db backup when completed?
February 12, 2008 at 5:41 am
You can use OS security to control who can access backup files or even to encrypt files.
You can also create a sql script (manually or from SQL Server Management Studio) with all your required tasks and put it into a job.
Best Regards Ramon
Regards Ramon
February 12, 2008 at 11:58 am
I created the script with sql back up, but when i try to insert that sccript into a maintenance plan with SMS, there is no option.
Like i said the ability to encrypt backed up data is my concern, if you can show me what you ment Ramon, by replying with a step by step process, i will appreciate it.
Thanks
February 12, 2008 at 12:12 pm
Have you considered a third-party product, i.e. Idera's SQLSafe, Quest's Lightspeed, or Redgate's SQLBackup. All offer support for both compression and encryption.
2008 will offer native compression but not encryption to the best of my knowledge (at least it wasn't available in the November CTP).
Tommy
Follow @sqlscribeFebruary 12, 2008 at 12:15 pm
Came across an article on SSC which should help you if you want to use EFS as an alternative 🙂
http://www.sqlservercentral.com/articles/Administering/implementing_efs/870/
Tommy
Follow @sqlscribeFebruary 12, 2008 at 10:51 pm
Andy Brown has a nice custom backup stored proc I use sometimes at http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/31640/[/url].
I tweaked it a bit by adding this code about a third of the way down:
IF @BackupProduct = 2
SELECT @FileExt = '.BAK'
ELSE
SELECT @FileExt = '.SLS'
IF @BackupType = 'L'
SELECT @FileExt = '.TRN' -- GMilner - added this change 7/20/2007 5:29:14 PM
because the transaction log backups had a .bak extension and I wanted .trn.
Perhaps you can work this into your solutions with something like Winzip's command-line interface for a "poor person's" backup->zip-encrypt or something.
Cheers.
G. Milner
February 13, 2008 at 3:42 am
Hi
Check this article from MS
Encrypting File System in Windows XP and Windows Server 2003
http://technet.microsoft.com/en-us/library/bb457065.aspx
Best practices for Encrypting File System
http://support.microsoft.com/kb/223316/
Regards Ramon
February 13, 2008 at 7:14 am
We use this sp which is run as a scheduled job each night. It will augomatically create backup jobs for each database (creating new jobs for new databases and removing jobs for databases that have been deleted from the server). Here is the SQL.
CREATE PROCEDURE [dbo].[USP_ControlBackupDB]
(
@BackupPath VARCHAR(255)
,@LogPath VARCHAR(255)
,@LogDaysRetainedINT = 7
,@MaintSlotStartCHAR(8) = '20:00:00'
,@MaintSlotEndCHAR(8) = '23:59:59'
,@info_flag CHAR(1) = 'N'
,@dbcc_speedCHAR(4) = 'FULL'
,@dump_optionVARCHAR(256) = 'WITH INIT, NOUNLOAD'
,@verify_flagCHAR(1) = 'Y'
,@All_butVARCHAR(2000) = NULL
,@None_butVARCHAR(2000) = NULL
,@DbOrderVARCHAR(2000) = NULL
)
AS
/********************************************************************************/
--
-- Procedure: USP_ControlBackupDB
--
-- Description: This procedure is the control layer for the DBA database
--maintenance (Backup DB) functionality. The following is performed:
--1. Delete jobs for databases that no longer exist
--2. Create new jobs for new databases
--3. Update the names of the log files to reflect the current date
--4. Update the scheduled time of each job to ensure an even spread
--
--
/********************************************************************************/
SET NOCOUNT ON
DECLARE
@errorsecINT
,@rowcountsecINT
,@execerrorINT
,@paramerrorINT
,@categoryidINT
,@jobidUNIQUEIDENTIFIER
,@dbnamesysname
,@devicenamesysname
,@logbasenamesysname
,@physicalnameNVARCHAR(260)
,@jobnamesysname
,@descriptionNVARCHAR(512)
,@jobstepidINT
,@jobstepnamesysname
,@commandNVARCHAR(3200)
,@outputfilenameNVARCHAR(200)
,@jobschedulenamesysname
,@activestartdateINT
,@activestarttimeINT
,@activeendtimeINT
,@maintstarttimeDATETIME
,@maintendtimeDATETIME
,@backupintervalINT
,@timeVARCHAR(8)
,@msgVARCHAR(256)
,@csvlistVARCHAR(2000)
,@csvindexINT
SET@errorsec = 0
SET@info_flag = UPPER(@info_flag)
SET@dbcc_speed = UPPER(@dbcc_speed)
SET@verify_flag = UPPER(@verify_flag)
--
-- Verify the supplied parameters
--
SET@paramerror = 0
CREATETABLE #FileExist (
FileExistsINT
,FileisaDirectoryINT
,ParentDirectoryExistsINT
)
-- Verify @BackupPath
IFRIGHT(@BackupPath,1) IN ( '\', '/' )
BEGIN
SELECT @BackupPath = LEFT(@BackupPath,LEN(@BackupPath)-1)
END
INSERT#FileExist
EXECmaster.sys.xp_fileexist @BackupPath
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
IF@rowcountsec <> 1
OREXISTS ( SELECT * FROM #FileExist WHERE FileExists = 1 OR FileisaDirectory = 0 OR ParentDirectoryExists = 0 )
BEGIN
PRINT 'The Backup Path ['+@BackupPath+'] does not exist or is not a directory'
SELECT@paramerror = @paramerror + 1
END
-- Verify @LogPath
DELETE#FileExist
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
IFRIGHT(@LogPath,1) IN ( '\', '/' )
BEGIN
SELECT @LogPath = LEFT(@LogPath,LEN(@LogPath)-1)
END
INSERT#FileExist
EXEC master.sys.xp_fileexist @LogPath
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
IF@rowcountsec <> 1
OREXISTS ( SELECT * FROM #FileExist WHERE FileExists = 1 OR FileisaDirectory = 0 OR ParentDirectoryExists = 0 )
BEGIN
PRINT 'The Log Path ['+@LogPath+'] does not exist or is not a directory'
SELECT@paramerror = @paramerror + 1
END
-- Verify @LogDaysRetained
IF@LogDaysRetained IS NULL
BEGIN
SET@LogDaysRetained = 7
END
-- Verify @MaintSlotStart
IFISDATE(CONVERT(CHAR(10),GETDATE(),120)+' '+@MaintSlotStart) = 0
ORISNULL(@MaintSlotStart,'') = ''
BEGIN
PRINT 'The maintenance slot start time ['+@MaintSlotStart+'] is invalid'
SELECT@paramerror = @paramerror + 1
END
ELSE
BEGIN
SET@maintstarttime = CAST(CONVERT(CHAR(10),GETDATE(),120)+' '+@MaintSlotStart AS DATETIME)
END
-- Verify @MaintSlotEnd
IFISDATE(CONVERT(CHAR(10),GETDATE(),120)+' '+@MaintSlotEnd) = 0
ORISNULL(@MaintSlotEnd,'') = ''
BEGIN
PRINT 'The maintenance slot end time ['+@MaintSlotEnd+'] is invalid'
SELECT@paramerror = @paramerror + 1
END
ELSE
BEGIN
SET@maintendtime = CAST(CONVERT(CHAR(10),GETDATE(),120)+' '+@MaintSlotEnd AS DATETIME)
END
-- Verify that @MaintSlotStart is before @MaintSlotEnd and that the difference is more than 60 minutes
IF@maintstarttime IS NOT NULL
AND@maintendtime IS NOT NULL
ANDDATEDIFF(mi,@maintstarttime,@maintendtime) <= 60
BEGIN
PRINT 'The maintenance slot is less than 60 minutes'
SELECT@paramerror = @paramerror + 1
END
-- Check for @info_flag values
IF@info_flag NOT IN ('Y','N')
BEGIN
SELECT @info_flag = 'N'
PRINT 'Allowable values for @info_flag are Y and N. Value set to N'
PRINT ' '
END
-- Check for @dbcc_speed values
IF@dbcc_speed NOT IN ('FULL','FAST')
BEGIN
SELECT @dbcc_speed = 'FULL'
PRINT 'Allowable values for @DBCC_SPEED are FULL and FAST. Value set to FULL'
PRINT ' '
END
-- Check for @verify_flag values
IF@verify_flag NOT IN ('Y','N')
BEGIN
SELECT @verify_flag = 'Y'
PRINT 'Allowable values for @verify_flag are Y and N. Value set to Y'
PRINT ' '
END
-- Verify @All_but
IFISNULL(@All_but,'') <> ''
BEGIN
SET@csvlist = @All_but
WHILEISNULL(@csvlist,'') <> ''
BEGIN
SET@csvindex = CHARINDEX(',',@csvlist)
IF@csvindex > 0
BEGIN
SET@dbname = SUBSTRING(@csvlist,1,@csvindex - 1)
SET @csvlist = SUBSTRING(@csvlist,@csvindex + 1,2000)
END
ELSE
BEGIN
SET@dbname = @csvlist
SET @csvlist = NULL
END
IFISNULL(@dbname,'') <> ''
ANDNOT EXISTS ( SELECT * FROM master.sys.databases WHERE name = @dbname )
BEGIN
PRINT 'Warning - The @All_but database name ['+@dbname+'] does not exist'
END
END
END
-- Verify @None_but
IFISNULL(@None_but,'') <> ''
BEGIN
SET@csvlist = @None_but
WHILEISNULL(@csvlist,'') <> ''
BEGIN
SET@csvindex = CHARINDEX(',',@csvlist)
IF@csvindex > 0
BEGIN
SET@dbname = SUBSTRING(@csvlist,1,@csvindex - 1)
SET @csvlist = SUBSTRING(@csvlist,@csvindex + 1,2000)
END
ELSE
BEGIN
SET@dbname = @csvlist
SET @csvlist = NULL
END
IFISNULL(@dbname,'') <> ''
ANDNOT EXISTS ( SELECT * FROM master.sys.databases WHERE name = @dbname )
BEGIN
PRINT 'Warning - The @None_but database name ['+@dbname+'] does not exist'
END
END
END
-- Verify that only one of @All_but and @None_but supplied
IFISNULL(@All_but,'') <> ''
ANDISNULL(@None_but,'') <> ''
BEGIN
PRINT 'Both @All_but and @None_but supplied'
SELECT@paramerror = @paramerror + 1
END
-- Abort if errors found
IF@paramerror > 0
BEGIN
PRINT'Parameter errors found - run aborted'
SET@errorsec = 50000
GOTOerrors_found
END
--
-- Create Job Category for 'DBA Maint Backup DB'
--
IFNOT EXISTS ( SELECT * FROM msdb.dbo.syscategories WHERE name = 'DBA Maint Backup DB' )
BEGIN
EXEC @execerror = msdb.dbo.sp_add_category 'JOB', 'LOCAL', 'DBA Maint Backup DB'
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
END
--
-- Select the category identity for 'DBA Maint Backup DB'
--
SELECT@categoryid = category_id
FROM msdb.dbo.syscategories
WHERE name = 'DBA Maint Backup DB'
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
IF@rowcountsec <> 1
BEGIN
PRINT 'Problem selecting category ''DBA Maint Backup DB'''
SELECT@errorsec = 50000
GOTOerrors_found
END
--
-- Obtain current list of databases
--
-- Temporary table used as we may not process the full list of databases i.e. tempdb
-- Ignore databases in Stand By mode
-- Restrict list of databases based on @All-but or @None-but parameters
--
CREATE TABLE #Database (
namesysname NOT NULL
)
IFISNULL(@All_but,'') <> ''
BEGIN
INSERT#Database (
name
)
SELECTname
FROMmaster.sys.databases
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
SET@csvlist = @All_but
WHILE@errorsec = 0
ANDISNULL(@csvlist,'') <> ''
BEGIN
SET@csvindex = CHARINDEX(',',@csvlist)
IF@csvindex > 0
BEGIN
DELETE#Database
WHEREname = SUBSTRING(@csvlist,1,@csvindex - 1)
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
,@csvlist = SUBSTRING(@csvlist,@csvindex + 1,2000)
END
ELSE
BEGIN
DELETE#Database
WHEREname = @csvlist
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
,@csvlist = NULL
END
END
END
ELSE
IFISNULL(@None_but,'') <> ''
BEGIN
SET@csvlist = @None_but
WHILE@errorsec = 0
ANDISNULL(@csvlist,'') <> ''
BEGIN
SET@csvindex = CHARINDEX(',',@csvlist)
IF@csvindex > 0
BEGIN
INSERT#Database (
name
)
SELECTname = SUBSTRING(@csvlist,1,@csvindex - 1)
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
,@csvlist = SUBSTRING(@csvlist,@csvindex + 1,2000)
END
ELSE
BEGIN
INSERT#Database (
name
)
SELECTname = @csvlist
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
,@csvlist = NULL
END
END
END
ELSE
BEGIN
INSERT#Database (
name
)
SELECTname
FROMmaster.sys.databases
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
END
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
--
-- Delete empty rows or rows for database names that do not exist
--
DELETET
FROM#Database T
WHEREISNULL(T.name,'') = ''
ORNOT EXISTS ( SELECT * FROM master.sys.databases D WHERE D.name = T.name )
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
--
-- Exclude tempdb and any database in standby mode
--
DELETET
FROM#Database T
WHERET.name = 'tempdb'
ORDATABASEPROPERTYEX(T.name,'IsInStandBy') = 1
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
--
-- Obtain current list of jobs for category 'DBA Maint Backup DB'
--
--13/06/2005 - AJL - Added extra column DbOrder
--
CREATE TABLE #Job (
job_idUNIQUEIDENTIFIER NOT NULL
,namesysname NOT NULL
,descriptionNVARCHAR(512) NOT NULL
,jobstepnamesysname NULL
,jobschedulename sysname NULL
,dbnamesysname NULL
,devicenamesysname NULL
,logbasenamesysname NULL
,dborderINT NULL DEFAULT 999999
)
INSERT#Job (
job_id
,name
,description
)
SELECTjob_id
,name
,description
FROMmsdb.dbo.sysjobs
WHEREcategory_id = @categoryid
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
--
-- Unpack the description to obtain the database name, the backup device name and the base log file name
--
-- The description should be formatted as follows:
-- Daily backup maintenance job created by USP_ControlBackupDB. JobStepName=[xxx],JobScheduleName=[xxx],
-- Database=[xxx],BackupDevice=[xxx],LogBaseName[xxx]
--
UPDATE#Job
SETjobstepname =
SUBSTRING(
SUBSTRING(description,CHARINDEX('JobStepName=[',description)+13,512)
,1
,CHARINDEX(']',SUBSTRING(description,CHARINDEX('JobStepName=[',description)+13,512))-1
)
,jobschedulename =
SUBSTRING(
SUBSTRING(description,CHARINDEX('JobScheduleName=[',description)+17,512)
,1
,CHARINDEX(']',SUBSTRING(description,CHARINDEX('JobScheduleName=[',description)+17,512))-1
)
,dbname =
SUBSTRING(
SUBSTRING(description,CHARINDEX('Database=[',description)+10,512)
,1
,CHARINDEX(']',SUBSTRING(description,CHARINDEX('Database=[',description)+10,512))-1
)
,devicename =
SUBSTRING(
SUBSTRING(description,CHARINDEX('BackupDevice=[',description)+14,512)
,1
,CHARINDEX(']',SUBSTRING(description,CHARINDEX('BackupDevice=[',description)+14,512))-1
)
,logbasename =
SUBSTRING(
SUBSTRING(description,CHARINDEX('LogBaseName=[',description)+13,512)
,1
,CHARINDEX(']',SUBSTRING(description,CHARINDEX('LogBaseName=[',description)+13,512))-1
)
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
--
-- Report on jobs that do not have the database name, backup device name or log base file name in the description
--
IFEXISTS ( SELECT * FROM #Job WHERE ISNULL(jobstepname,'') = ''
OR ISNULL(jobschedulename,'') = ''
OR ISNULL(dbname,'') = ''
OR ISNULL(devicename,'') = ''
OR ISNULL(logbasename,'') = '' )
BEGIN
PRINT'Error the following Jobs are incorrectly formatted for DB maintenance backups'
PRINT' '
SELECT*
FROM#Job
WHERE ISNULL(jobstepname,'') = ''
OR ISNULL(jobschedulename,'') = ''
OR ISNULL(dbname,'') = ''
OR ISNULL(devicename,'') = ''
OR ISNULL(logbasename,'') = ''
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
SET@errorsec = 50000
GOTO errors_found
END
END
--
-- Delete the msdb jobs for databases that no longer exist
--
WHILEEXISTS ( SELECT * FROM #Job J WHERE NOT EXISTS ( SELECT * FROM #Database D WHERE J.dbname = D.name))
BEGIN
SELECTTOP 1
@jobid = J.job_id
,@dbname = J.dbname
,@devicename = J.devicename
,@logbasename = J.logbasename
FROM#Job J
WHERENOT EXISTS ( SELECT * FROM #Database D WHERE J.dbname = D.name)
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Delete the job
EXEC@execerror = msdb.dbo.sp_delete_job @job_id = @jobid
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Delete the dump device
EXEC@execerror = master.sys.sp_dropdevice
@logicalname = @devicename
,@delfile = 'delfile'
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Delete the Jobstep log files
EXEC@execerror = dbo.USP_TidyJobstepLogs
@LogPath = @LogPath
,@LogBaseName= @logbasename
,@DaysRetained= -1
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Delete the job details
DELETE#Job
WHEREjob_id = @jobid
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
END
--
-- Create msdb jobs for new databases that are not currently maintained
--
WHILEEXISTS ( SELECT * FROM #Database D WHERE NOT EXISTS ( SELECT * FROM #Job J WHERE D.name = J.dbname))
BEGIN
SELECTTOP 1
@dbname = D.name
,@devicename = 'Local_'+D.name+'_DBBackup'
,@logbasename = D.name+'DBBackup'
FROM#Database D
WHERENOT EXISTS ( SELECT * FROM #Job J WHERE D.name = J.dbname)
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Create the dump device
SET@physicalname = @BackupPath+'\'+@dbname+'DBBackup.bak'
IFEXISTS ( SELECT * FROM master.sys.backup_devices WHERE name = @devicename)
BEGIN
IFNOT EXISTS ( SELECT * FROM master.sys.backup_devices WHERE name = @devicename AND physical_name = @physicalname)
BEGIN
PRINT'Device name ['+@devicename+'] already exists but for a different physical file'
PRINT'Database ['+@dbname+'] ignored'
DELETE#Database
WHEREname = @dbname
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
END
END
ELSE
BEGIN
EXEC@execerror = master.sys.sp_addumpdevice
'disk'
,@devicename
,@physicalname
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
END
-- Create the job
SET@jobid = NULL
SET@jobname = 'DBA Maint Backup DB '+@dbname
SET@jobstepname = 'Backup DB '+@dbname
SET@jobschedulename = 'Backup DB '+@dbname+' Schedule'
SET@description = 'Daily backup maintenance job created by USP_ControlBackupDB. JobStepName=['+
@jobstepname+'],JobScheduleName=['+@jobschedulename+'],Database=['+
@dbname+'],BackupDevice=['+@devicename+'],LogBaseName=['+@logbasename+']'
EXEC@execerror = msdb.dbo.sp_add_job
@job_name = @jobname
,@enabled = 1
, @description = @description
, @start_step_id = 1
,@category_name = NULL
, @category_id = @categoryid
,@owner_login_name = NULL
, @notify_level_eventlog = 2
, @notify_level_email = 2
, @notify_level_netsend = 0
, @notify_level_page = 0
, @notify_email_operator_name = 'DBAdmins'
, @notify_netsend_operator_name = NULL
, @notify_page_operator_name = NULL
, @delete_level = 0
, @job_id = @jobid OUTPUT
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Create the jobstep
SET@command = 'EXEC USP_BackupDB '''+
@dbname+''','''+
@devicename+''','''+
@info_flag+''','''+
@dbcc_speed+''','''+
@dump_option+''','''+
@verify_flag+''''
SET@outputfilename = @LogPath+'\'+@logbasename+CONVERT(CHAR(8),GETDATE(),112)+'.log'
EXEC@execerror = msdb.dbo.sp_add_jobstep
@job_id = @jobid
, @step_id = 1
, @step_name = @jobstepname
, @subsystem = 'TSQL'
,@command = @command
, @additional_parameters = NULL
, @cmdexec_success_code = 0
,@on_success_action = 1
, @on_success_step_id = 0
,@on_fail_action = 2
, @on_fail_step_id = 0
, @server = NULL
, @database_name = 'DBA'
, @database_user_name = NULL
, @retry_attempts = 0
, @retry_interval = 0
, @output_file_name = @outputfilename
, @flags = 2
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Create the job schedule
SET@activestartdate = CAST(CONVERT(CHAR(8),GETDATE(),112) AS INT)
SET@activestarttime = CAST(SUBSTRING(@MaintSlotStart,1,2)+SUBSTRING(@MaintSlotStart,4,2)+SUBSTRING(@MaintSlotStart,7,2) AS INT)
SET@activeendtime = CAST(SUBSTRING(@MaintSlotEnd,1,2)+SUBSTRING(@MaintSlotEnd,4,2)+SUBSTRING(@MaintSlotEnd,7,2) AS INT)
EXEC@execerror = msdb.dbo.sp_add_jobschedule
@job_id = @jobid
, @name = @jobschedulename
, @enabled = 1
, @freq_type = 4
, @freq_interval = 1
, @freq_subday_type = 1
, @freq_subday_interval = 0
, @freq_relative_interval = 0
, @freq_recurrence_factor = 0
, @active_start_date = @activestartdate
, @active_end_date = 99991231
, @active_start_time = @activestarttime
, @active_end_time = @activeendtime
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Create the job server
EXEC@execerror = msdb.dbo.sp_add_jobserver
@job_id = @jobid
,@server_name = N'(local)'
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Insert the new job details
INSERT#Job (
job_id
,name
,description
,jobstepname
,jobschedulename
,dbname
,devicename
,logbasename
)
SELECT@jobid
,@jobname
,@description
,@jobstepname
,@jobschedulename
,@dbname
,@devicename
,@logbasename
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
END
--
-- Loop through the database backup jobs in ascending database name order and update the following:
-- 1. Tidy the jobstep log files to the number of days to be retained
-- 2. Update the jobstep log file name to reflect todays date.
-- 2a.Set the database order - 13/06/2005 - AJL
-- 3. Update the scheduled time to ensure an even spread over the maintenance slot.
--
--
-- Verify @DbOrder
IFISNULL(@DbOrder,'') <> ''
BEGIN
SET@csvlist = @DbOrder
WHILEISNULL(@csvlist,'') <> ''
BEGIN
SET@csvindex = CHARINDEX(',',@csvlist)
IF@csvindex > 0
BEGIN
SET@dbname = SUBSTRING(@csvlist,1,@csvindex - 1)
SET @csvlist = SUBSTRING(@csvlist,@csvindex + 1,2000)
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
,@csvlist = SUBSTRING(@csvlist,@csvindex + 1,2000)
END
ELSE
BEGIN
SET@dbname = @csvlist
SET @csvlist = NULL
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
,@csvlist = NULL
END
IFISNULL(@dbname,'') <> ''
ANDNOT EXISTS ( SELECT * FROM master.sys.databases WHERE name = @dbname )
BEGIN
PRINT 'Warning - The @DbOrder database name ['+@dbname+'] does not exist'
END
END
END
--Set the Db Order
IFISNULL(@DbOrder,'') <> ''
BEGIN
declare @Seq int
set @Seq = 0
SET@csvlist = @DbOrder
WHILE@errorsec = 0
ANDISNULL(@csvlist,'') <> ''
BEGIN
SET@csvindex = CHARINDEX(',',@csvlist)
IF@csvindex > 0
BEGIN
SET@dbname = SUBSTRING(@csvlist,1,@csvindex - 1)
UPDATE#Job
SETdborder = @Seq
WHEREdbname = @dbname
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
,@csvlist = SUBSTRING(@csvlist,@csvindex + 1,2000)
END
ELSE
BEGIN
UPDATE#Job
SETdborder = @Seq
WHEREdbname = @csvlist
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
,@csvlist = NULL
END
--Increment the sequence number
set @Seq = @Seq + 1
END
END
-- Calculate the backup interval
SET@backupinterval = DATEDIFF(ss,@maintstarttime,@maintendtime) / ( SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE COUNT(*) END FROM #Job)
WHILEEXISTS ( SELECT * FROM #Job )
BEGIN
SELECT@jobid = J.job_id
,@jobname = J.name
,@description = J.description
,@jobstepname = J.jobstepname
,@jobschedulename = J.jobschedulename
,@dbname = J.dbname
,@devicename = J.devicename
,@logbasename = J.logbasename
FROM#Job J
WHEREJ.dbname = ( SELECT top 1 J2.dbname FROM #Job J2 order by dborder,name ) --13/06/2005 - AJL
--WHEREJ.dbname = ( SELECT MIN(J2.dbname) FROM #Job J2 ) --13/06/2005 - AJL
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Tidy the Jobstep log files
EXEC@execerror = dbo.USP_TidyJobstepLogs
@LogPath = @LogPath
,@LogBaseName= @logbasename
,@DaysRetained= @LogDaysRetained
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Update the jobstep log file name
SELECT@jobstepid = step_id
FROMmsdb.dbo.sysjobsteps
WHEREjob_id = @jobid
ANDstep_name = @jobstepname
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
SET@outputfilename = @LogPath+'\'+@logbasename+CONVERT(CHAR(8),GETDATE(),112)+'.log'
EXEC@execerror = msdb.dbo.sp_update_jobstep
@job_id = @jobid
, @step_id = @jobstepid
, @output_file_name = @outputfilename
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Update the scheduled time
SET@time = CONVERT(CHAR(8),@maintstarttime,108)
SET@activestarttime = CAST(SUBSTRING(@time,1,2)+SUBSTRING(@time,4,2)+SUBSTRING(@time,7,2) AS INT)
SET@time = CONVERT(CHAR(8),@maintendtime,108)
SET@activeendtime = CAST(SUBSTRING(@time,1,2)+SUBSTRING(@time,4,2)+SUBSTRING(@time,7,2) AS INT)
EXEC@execerror = msdb.dbo.sp_update_jobschedule
@job_id = @jobid
, @name = @jobschedulename
, @active_start_time = @activestarttime
, @active_end_time = @activeendtime
SELECT@errorsec = CASE WHEN @@error = 0 THEN @execerror ELSE @@error END
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Delete the job details
DELETE#Job
WHEREdbname = @dbname
SELECT@errorsec = @@error
,@rowcountsec = @@rowcount
IF@errorsec <> 0
BEGIN
GOTOerrors_found
END
-- Increment the start time for the next backup
SET@maintstarttime = DATEADD(ss,@backupinterval,@maintstarttime)
END
GOTOend_of_procedure
errors_found:
--
-- Ensure that any calling code is informed of the error
--
SET@msg = 'USP_ControlBackupDB Failed with error '+CAST(@errorsec AS VARCHAR(30))
RAISERROR(@msg,16,1)
end_of_procedure:
--
-- Standard exit point from the procedure
--
return (@errorsec)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply