April 7, 2003 at 10:29 am
I have a maintenance plan which backs-up all of our databases to a network server. The job has been failing at least 3 times a week recently, most of the time on one particular database, the largest one.
The SQL Error log states: 2003-04-07 09:17:00.23 kernel BackupDiskFile::RequestDurableMedia: failure on backup device '\\Network Server\directory\database.BAK'. Operating system error 240(The session was cancelled.).
When I look in the Event Viewer I see an error "The redirector has timed out a request to servername"
The strange thing is that the backup file is actually created on the server even though the job states that ity has failed.
I am not sure where the issue lies, either with the SQL Server or our network. Any help will be greatly appreciated.
April 7, 2003 at 12:18 pm
Maybe you should map the network directory to wich SQL saves the backup. To map the drive you can execute :
exec master.dbo.xp_cmdshell 'NET USE H: \\Network Server\Shareddirectory' /User:domainuser password'
Then execute the backup to Disk ='H:\Database.bak' and in another step, disconnect from the mapped driver with:
exec master.dbo.xp_cmdshell 'NET USE H: /DELETE'.
You must map the driver from inside SQL server to allow the agent to actually see tha mapping letter.
April 7, 2003 at 12:26 pm
I would verify the backup that was created in the netwrok server is vaild even though the backup file was actually created. You might encounter network break down or network server failure during the backup.
April 8, 2003 at 6:42 am
I was able to restore the database from the backup file created.
April 10, 2003 at 8:08 am
I am not familiar with writing scripts to perform the backup operations for all of my databases. The maintenance plan was created using the GUI in Enterprise Manager. Should I create a drive mapping in Explorer and then backup the databases to that drive?
It is very strange that the backup only fails periodically, 3 times a week and it is usually on the same database.
April 10, 2003 at 3:13 pm
Do you have any other SQL jobs which execute the DBCC and Create Index stmts.B'caz I had the same problem with my backup software and I wrote a generic proc to take all the DB backups.If you need the script, Let me know.
April 11, 2003 at 7:37 am
It would be great if you could send me the script. Could the script also contain code to remove filed that are older than 4 days?
April 11, 2003 at 10:06 am
I have a SQL proc which takes all the DB full back up on weekly and Diff. backup on daily.
I have a Active x script on DTS which deletes the Old back ups from the drive.If you are ok with this scripts.Let me know.
April 11, 2003 at 1:47 pm
It would be great if I could see the script.
April 11, 2003 at 3:47 pm
I had the same problem with similar error messages. My job backs up more than one hundred databases. about 30 each with 3 GB, the others are small one. I run it once a month. Every time always have a few big databases backup failed. Actually the backup is completed.
Anybody knows the problem?
Robert
April 11, 2003 at 4:09 pm
Robert,
Do you backup your databases using maintenance plan? If you are, double click the plan, choose reporting tab and check "write report a text file in directory". Once backup job runs completely, go to that directory to review report file and you may find something related to your job failure.
Backup databases once a month is too risk.
April 11, 2003 at 4:31 pm
Hey this is the SQL proc which backs up all the DBs
/*******************************************************************************************************************************************************************************************************************************************************/
-- Name:Proc_AllDB_Daily_DiskBackup
-- Description:
--Basicly, this script just loops thru all the databases on a given server and backs them up somewhere (We can set the path) on that machine.
--From there, We can compress them and transfer them off site, etc.
--The backup will include MSDB and Master databases for complete disaster recovery.
--This script is meant TO run daily. ON Saturday, it will perform FULL backups OF each DATABASE AND on the other days it does a differential backup.
--But we take the FULL BACKUP of MASTER DB on daily.
--This script will NOT overwrite ANY files, it stores the backups IN the following format:dbname_(full OR diff)_20030221.bak
--If we start this process as a New one before SAt'day then we should perform the FULL backup and schedule the proc.
--Author: Dhiva
--Date:02/21/2003
--Log:
/*******************************************************************************************************************************************************************************************************************************************************/
CREATE Procedure dbo.Proc_AllDB_Daily_DiskBackup
AS
BEGIN
/*******************************************************************************************************************************************************************************************************************************************************/
--Declare our variables
/*******************************************************************************************************************************************************************************************************************************************************/
DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @IsSaturday bit
DECLARE @Name varchar(30), @MediaName varchar(30), @HasFullBackup bit, @BackupDirectory nvarchar(200)
/*******************************************************************************************************************************************************************************************************************************************************/
--Set the backup Shared directory
/*******************************************************************************************************************************************************************************************************************************************************/
SET @BackupDirectory = '\\network drive\'
/*******************************************************************************************************************************************************************************************************************************************************/
--Chk for the (Date) Saturday
--See IF its Saturday.. 1=is Saturday, 0=is NOT Saturday
/*******************************************************************************************************************************************************************************************************************************************************/
SET @IsSaturday = CASE Datepart(dw, CURRENT_TIMESTAMP)
WHEN 7 THEN
1
ELSE
0
END
/*******************************************************************************************************************************************************************************************************************************************************/
--Loop thru all the databases that we should backup.
/*******************************************************************************************************************************************************************************************************************************************************/
DECLARE Database_Cursor cursor FOR SELECT name FROM master.dbo.sysdatabases WHERE name <> 'tempdb' AND name <> 'model' and name <> 'Northwind'
OPEN Database_Cursor
FETCH next FROM Database_Cursor INTO @DB
WHILE @@fetch_status = 0
begin
--Set SOME stuff so it looks pretty IN sql server
SET @Name = @DB + '( Daily BACKUP )'
SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
/*******************************************************************************************************************************************************************************************************************************************************/
--Check TO see IF this db has a FULL backup. If its the master, we have to do a full backup.
--If we run this Proc against the New DB, Then automatically it performs the FULL back for the new DBs.
--If the @HasFullBackup = 0 the No full backup and We have to perform the full backup
--If the @HasFullBackup = 1 then it has the full backup and we should perform the Diff backup
/*******************************************************************************************************************************************************************************************************************************************************/
SET @HasFullBackup = 0
IF (select count(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
BEGIN
If @DB <>'Master'
Begin
/*******************************************************************************************************************************************************************************************************************************************************/
--This User Defind DATABASE has a FULL backup.
--Get the Backup file name and Description according to the backups
/*******************************************************************************************************************************************************************************************************************************************************/
SET @HasFullBackup = 1
SET @BackupFile = @BackupDirectory + @DB + '_' +
CASE @IsSaturday
WHEN 1 THEN
'Full'
ELSE
'Diff'
END
+ '_' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
SET @Description =CASE @IsSaturday
WHEN 1 THEN
'Full'
ELSE
'Differential'
END
+ ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
End
Else
/*******************************************************************************************************************************************************************************************************************************************************/
--This User Defind DATABASE hasn't a FULL backup.
--Get the Backup file name and Description according to the backups
/*******************************************************************************************************************************************************************************************************************************************************/
Begin
SET @HasFullBackup = 0
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
End
END
ELSE
/*******************************************************************************************************************************************************************************************************************************************************/
--This DB is a MASTER DATABASE and Get the full backup
--Get the Backup file name and Description according to the backups
/*******************************************************************************************************************************************************************************************************************************************************/
BEGIN
SET @HasFullBackup = 0
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
END
/*******************************************************************************************************************************************************************************************************************************************************/
--If it IS Saturday, or the CURRENT db is master, or if this db has NOT had a FULL backup, the do a full backup.
/*******************************************************************************************************************************************************************************************************************************************************/
IF @IsSaturday = 1 or @DB = 'master' or @HasFullBackup = 0
BEGIN
BACKUP DATABASE @DB
TO DISK = @BackupFile
WITH NAME = @Name,
DESCRIPTION = @Description ,
MEDIANAME = @MediaName,
MEDIADESCRIPTION = @Description ,
STATS = 10
print 'Full backup'
END
/*******************************************************************************************************************************************************************************************************************************************************/
--This would be a daily differentail backup.(Except Master DB)
/*******************************************************************************************************************************************************************************************************************************************************/
IF @IsSaturday = 0 AND @DB <> 'master' and @HasFullBackup <> 0
BEGIN
BACKUP DATABASE @DB
TO DISK = @BackupFile
WITH DIFFERENTIAL,
NAME = @Name,
DESCRIPTION = @Description ,
MEDIANAME = @MediaName,
MEDIADESCRIPTION = @Description ,
STATS = 10
Print 'Diff Backup'
END
/*******************************************************************************************************************************************************************************************************************************************************/
--Print debug.
/*******************************************************************************************************************************************************************************************************************************************************/
PRINT '------------------------------------------------------------------------'
PRINT ''
PRINT 'DB: ' + @DB
PRINT 'Name: ' + @Name
PRINT 'Media Name: ' + @MediaName
PRINT 'Backup File: ' + @BackupFile
PRINT 'Description: ' + @Description
PRINT 'Is Saturday (0 OR 1): ' + str(@IsSaturday)
PRINT 'DB has FULL BACKUP (0 or 1): ' + str(@HasFullBackup)
PRINT ''
PRINT '------------------------------------------------------------------------'
PRINT '------------------------------------------------------------------------'
print''
FETCH next FROM Database_Cursor INTO @DB
/*******************************************************************************************************************************************************************************************************************************************************/
end
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
/*******************************************************************************************************************************************************************************************************************************************************/
END
GO
This Script will take care of the OLD back up files.
'***************************************************************************************************************************************
' Visual Basic ActiveX Script
'This Script used to find the files or list the file on the specified dir.
'***************************************************************************************************************************************
Option Explicit
Function Get_Delete_Old_Files_Delete_File()
'***************************************************************************************************************************************
' Declare FSO Related Variables
'***************************************************************************************************************************************
Dim sFolder
Dim fso
Dim fsoFolder
Dim fsoFile
Dim sFileName
Dim sDate
Dim sDateNo
Dim sDateFull
Dim sDateFullNo
Dim sDateDelNo
'***************************************************************************************************************************************
' Import Folder Or Source & Destination folder
'***************************************************************************************************************************************
sFolder = "\\net work\DBBACKUP\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
'***************************************************************************************************************************************
'Get the date value or find the date
'sDatefull date value to handle the Full backup files
'sDate date value is to handle the Diff backup files.
'keep 3 days recent diff. back up and delete the old diff back ups
'Keep the latest 1 full back up of all db.
'keep all the master Full DB back up.
'***************************************************************************************************************************************
sDate = DateValue( date )-3
sDateFull = DateValue(date)-7
'msgbox (sDate)
'***************************************************************************************************************************************
'Convert the date filed into integer and If the day or month value is single digit then concodinate with the "0" value.
'***************************************************************************************************************************************
'For Diff backup date
If month(sDate) <10 Then
If day(sDate)<10 then
sDateNo = year(sDate) & "0" & month(sDate) & "0" & day(sDate)
Else
sDateNo = year(sDate) & "0" & month(sDate) & day(sDate)
End If
Else
If day(sDate)<10 then
sDateNo = year(sDate) & month(sDate) & "0" & day(sDate)
Else
sDateNo = year(sDate) & month(sDate) & day(sDate)
End If
End if
'msgbox "DIFF Backup Date : " & (sDateNo)
'For full backup date
If month(sDateFull) <10 Then
If day(sDateFull)<10 then
sDateFullNo = year(sDateFull) & "0" & month(sDateFull) & "0" & day(sDateFull)
Else
sDateFullNo = year(sDateFull) & "0" & month(sDateFull) & day(sDateFull)
End If
Else
If day(sDateFull)<10 then
sDateFullNo = year(sDateFull) & month(sDateFull) & "0" & day(sDateFull)
Else
sDateFullNo = year(sDateFull) & month(sDateFull) & day(sDateFull)
End If
End if
'msgbox " FULL Backup Date : " & (sDateFullNo)
'***************************************************************************************************************************************
'List all the files which is on the SQL backup dir. and find the file which has the date value(Old file),and delete it.
'***************************************************************************************************************************************
For Each fsoFile in fsoFolder.Files
' Get filenme
sFileName = sFolder & fsoFile.Name
'MsgBox(Left(right(sFileName,17),13))
'Set the Date for deletion
If Instr(sfilename, "Diff_") >1 OR Instr(sfilename, "Master_") >1 Then
'Msgbox "Diff OR MASTER"
sDateDelNo = sDateNo
ElseIf Instr(sfilename, "Full_") >1 Then
'Msgbox "ONLY FULL"
sDateDelNo = sDateFullNo
End If
If Left(right(sFileName,12),8) <= sDateDelNo Then
'Msgbox ( sFileName)
'Delete the Selected file from the target place
fso.DeleteFile sFileName
End If
'Exit For
Next
Get_Delete_Old_Files_Delete_File = DTSTaskExecResult_Success
End Function
April 15, 2003 at 1:18 pm
Hi Allen,
I checked the error log file. It says "The specified network name is no longer available".
In fact, I backed up all DBs to the same server with the same network path. some are ok some are failed. and I am quite sure, during the backup time, no body would access that path.
This server just store the monthly data. Input data once a month then no change, so I just backup it to network disk once a month.
We do backup it to tape everyday using veritas.
Robert
April 15, 2003 at 2:38 pm
I would ask NT / network administrator to monitor the network connection between your SQL Server and the server that keeps your database backups during your monthly backup jobs run and make sure the share drive is avaliable during that backup time period.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply