July 1, 2011 at 8:20 am
Hi,
I have set up and agent job which passes a list of databases on my sever into a cursor and then looping through the list and backing up each database.
This works fine as long as there are not any errors.
If i try to back up a newly created database before first creating the directory for that database i get the following error:
"BackupDiskFile::CreateMedia: Backup Device 'x.bak' failed to create. Operating system error 3(error not found)."
The problem is that i would like the cusror to continue processing the remaining backups despite this error being raised but it is not doing; the job simply fails and the remaining databases are not backed up.
Does anyone know how i can make the job continue running even after the error has been thrown?
My code is as follows:
Agent Job
Declare @DatabaseName varchar(250)
Declare Cusror_DBNames Cursor For select Name as DatabaseName from sys.databases where Name not in ('tempdb','asdb') AND NAME < 'CashSales_Test' order by Name
Open Cusror_DBNames
FETCH NEXT FROM Cusror_DBNames INTO @DatabaseName
While (@@FETCH_STATUS <> -1)
BEGIN
exec dbo.Backup_Database @DatabaseName
FETCH NEXT FROM Cusror_DBNames INTO @DatabaseName
end
Close Cusror_DBNames
Deallocate Cusror_DBNames
GO
Stored proc
SET NOCOUNT ON;
DECLARE @FILENAME varchar(250)
SELECT@FILENAME = 'D:\SQLBackup\' + @DBName + '\' + @DBName + '_' +
left(replace(replace(replace(CONVERT(VARCHAR(19), GETDATE(), 120),'-',''),':',''),' ',''),12) +
'.bak'
BEGIN TRY
BACKUP DATABASE @DBName
TO DISK = @FILENAME
END TRY
BEGIN CATCH
DECLARE @Subject varchar(250)
set @Subject = 'Full database backup failed for x.[' + rtrim(@DBName) + ']'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseAdmin',
@recipients = x@x.co.uk',
@body = '',
@subject = @Subject;
END CATCH
July 1, 2011 at 8:37 am
I don't think there's a way to do that in T-SQL. My solution on similar scripts has been to check for the existence of the folder, and have it created, before running the backup command. A very simple CLR proc can check for the folder and, if not found, create it, then pass control back to the backup proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2011 at 8:38 am
Thanks for the advice; i'll give it a go.
July 1, 2011 at 10:46 am
That must be an issue with 2005 Agent because the setup works fine in 2008 Agent, i.e. it keeps going even after an error has occurred from not being able to backup the database due to a missing path. Now, if you're also encountering an error in your proc's CATCH block (say from trying to send mail) then the way you're setup inside the cursor where the proc call itself is not happening in a TRY/CATCH block it will definitely stop the job.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 1, 2011 at 11:07 am
opc.three (7/1/2011)
That must be an issue with 2005 Agent because the setup works fine in 2008 Agent, i.e. it keeps going even after an error has occurred from not being able to backup the database due to a missing path. Now, if you're also encountering an error in your proc's CATCH block (say from trying to send mail) then the way you're setup inside the cursor where the proc call itself is not happening in a TRY/CATCH block it will definitely stop the job.
That's a good point. Might wrap the execution call to the proc in a try catch of its own. Inside the cursor, that is.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2011 at 11:19 am
GSquared (7/1/2011)
I don't think there's a way to do that in T-SQL. My solution on similar scripts has been to check for the existence of the folder, and have it created, before running the backup command. A very simple CLR proc can check for the folder and, if not found, create it, then pass control back to the backup proc.
Could also, instead of using a CLR, use an extended procedure ... i think that it was xp_cmdshell which you can use to check and create directories.
July 1, 2011 at 11:56 am
kramaswamy (7/1/2011)
GSquared (7/1/2011)
I don't think there's a way to do that in T-SQL. My solution on similar scripts has been to check for the existence of the folder, and have it created, before running the backup command. A very simple CLR proc can check for the folder and, if not found, create it, then pass control back to the backup proc.Could also, instead of using a CLR, use an extended procedure ... i think that it was xp_cmdshell which you can use to check and create directories.
<not_bashing>xp_CmdShell will work, but it deserves a disclaimer. CLR is the far better option here in terms of security. Don't get me started on the amount of baggage xp_CmdShell arrives with in terms of what you need to do to secure your instance after enabling it.</not_bashing>
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 1, 2011 at 11:58 am
Yeah I know - I wouldn't recommend using xp_cmdshell, but if for whatever reason he doesn't have access to a visual studio environment to create a CLR with, then it might be an option.
July 1, 2011 at 12:10 pm
kramaswamy (7/1/2011)
Yeah I know - I wouldn't recommend using xp_cmdshell, but if for whatever reason he doesn't have access to a visual studio environment to create a CLR with, then it might be an option.
Here is a pre-compiled set of CLR functions from a person that I know does a lot of work in the community:
http://filesystemhelper.codeplex.com/[/url]
Personally I do not believe in accessing the file system directly from within T-SQL code. And while I have not used these myself nor have I looked at the code chances are they're more than ample for db admin work. This is just one lib of many too. There are tons of pre-compiled options out there that do not require writing or compiling any CLR code.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 1, 2011 at 12:18 pm
Just for the record, I prefer PowerShell for this kind of work. It can do all the file system and T-SQL stuff with ease all within the same programming domain.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2011 at 3:48 am
Thanks for your contributions; i have been out of the office this week but i will now work through your suggestions.
I will post back the results.
July 6, 2011 at 8:35 am
FYI wrapping the stored procedure call in a try catch did not solve the problem; the error is still thrown and the job stops executing.
July 6, 2011 at 9:19 am
DBANewbie (7/6/2011)
FYI wrapping the stored procedure call in a try catch did not solve the problem; the error is still thrown and the job stops executing.
I just tried this in 2005 Agent, just to see, and the job did fail. It must be something with 2005 where Agent does not honor the script to allow its own errors.
What you could do is try kicking off your script using SqlCmd in a CmdExec job step type to see if it will honor the TRY/CATCH in your T-SQL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 13, 2011 at 2:13 am
Hello,
I have a similar issue with my database restore script (on SQL Server 2005 SP3).
The script loops into a database name list and is designed to continue if any error arises.
Error handling works fine when it runs directly from SSMS but not when it runs from a SQL Agent job, the job is terminated once an error is thrown.
I have read than SQL Agent terminates a job step on any error with severity level > 10 🙁
As suggested try running your script from sqlcmd.
July 13, 2011 at 2:20 am
Thanks for the advice; i will give this a try.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply