March 23, 2009 at 3:33 am
I have two scheduled backups on two different servers.
1>The first backup plan is a full backup on Sunday and differential backup from Monday to Saturday.For both the backp it is a direct
T-sql statement in job schedule.
My differential backup t-sql is as shown below.
BACKUP DATABASE DB_Name
TO DISK='D:\Folder1\DB_Name.BAK' WITH DIFFERENTIAL,
RETAINDAYS = 7,
NOFORMAT,
NOINIT
The problem is the backup is overwritting the previous differential backup
instead of appending the previous backup inspite of using the NOINIT.
Is there any wrong in the query?
2>In another server which is no where linked or related to the first server I have created a database maintainance plan for full backup on very day. I have used two task Backup database task and Execute T-sql task. I have written a query in Execute T-sql task to clean up the old database backup which is more than two days and created a connection from this to Backup task.The destination folder is over the network in another server.
The problem is the schedule backups fail to backup over network.
If I manually start the job then the backups do happen.
Even when the schedule backup failed for consecutive 3 or more days
the clean up task had removed the older backup.
First I checked the job history in Jobs, this is what I found
BackupMedium::ReportIoError: write failure on backup device '\\server\folder1\db_name\db_name_backup_200903210202.bak'. Operating system error 64(The specified network name is no longer available.).
I checked the log file and this message was written.
Failed:(-1073548784) Executing the query "BACKUP DATABASE [db_name] TO DISK = N'\\\\server\\db_name_Backup\\db_name\\db_name_backup_200903230201.bak' WITH NOFORMAT, NOINIT, NAME = N'db_name_backup_20090323020157', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "A nonrecoverable I/O error occurred on file "\\\\Server\\db_name_Backup\\db_name\\db_name_backup_200903230201.bak:" 64(The specified network name is no longer available.).
BACKUP DATABASE is terminating abnormally.
Why do the schedule backup fails?
March 23, 2009 at 3:59 am
Hi Ashwin,
1. Just try this
BACKUP DATABASE DB_Name
TO DISK='D:\Folder1\DB_Name.BAK' WITH DIFFERENTIAL,
RETAINDAYS = 7
2. Make sure that the SQL Server Agent start up account and the owner of the job has access to the share to which you are taking the backup.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 23, 2009 at 4:32 am
1> I had tried this earlier and it does'nt work.I tried even now but it overwrites the old backup.
2> The owner of the job is sa
The agent runs in local account and that account has permission to destination folder in different server.
March 23, 2009 at 4:34 am
2) Try changing the "Owner" of the job to the Account which has permissions on the remote share.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 23, 2009 at 6:45 am
Do you think changing the owner of the job works.What confuses me is that when the same backup job is started manually then the job succeeds but fails for schedule time. Even though the job fails for
backup the execute t-sql task would have removed the backup which is older than two days.How this can happen? If there is no permission to access that folder.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply