backup failed

Question

backup failed

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 1

Hey,

Ok, had a look at backup script and its one I wouldn't put into my production environment. I would strongly suggest you rewrite your script and test it before deploying into a production environment. Additionally, you either want to write to a backup device or file, but not both and watch your date formatting on file names.

Regards,

Phillip Cox

madhu-686862 SSChampion Points: 10707 More actions · Answer 2

Thank you..

This is the error I can see in the error log:

2008-09-16 03:21:20.86 spid59 BackupMedium::ReportIoError: write failure on backup device '\\advfuji\d$\Backup\AQSSQL2\CFQUTN01\CFQUTN01_09-16-2008.bak'. Operating system error 112(There is not enough space on the disk.). 0

2008-09-16 03:21:20.86 spid59 Internal I/O request 0x4AD61BC0: Op: Write, pBuffer: 0x081C0000, Size: 983040, Position: 13327474176, UMS: Internal: 0x0, InternalHigh: 0xF0000, Offset: 0x1A611E00, OffsetHigh: 0x3, m_buf: 0x081C0000, m_len: 983040, m_actu 0

alBytes: 0, m_errcode: 112, BackupFile: \\advfuji\d$\Backup\AQSSQL2\CFQUTN01\CFQUTN01_09-16-2008.bak 1

2008-09-16 03:21:20.86 backup BACKUP failed to complete the command Exec DB_BACKUP_AQSSQL2 'CFQUTN01'

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 3

madhu.arda (9/16/2008)
Thank you..
This is the error I can see in the error log:
2008-09-16 03:21:20.86 spid59 BackupMedium::ReportIoError: write failure on backup device '\\advfuji\d$\Backup\AQSSQL2\CFQUTN01\CFQUTN01_09-16-2008.bak'. Operating system error 112(There is not enough space on the disk.). 0
2008-09-16 03:21:20.86 spid59 Internal I/O request 0x4AD61BC0: Op: Write, pBuffer: 0x081C0000, Size: 983040, Position: 13327474176, UMS: Internal: 0x0, InternalHigh: 0xF0000, Offset: 0x1A611E00, OffsetHigh: 0x3, m_buf: 0x081C0000, m_len: 983040, m_actu 0
alBytes: 0, m_errcode: 112, BackupFile: \\advfuji\d$\Backup\AQSSQL2\CFQUTN01\CFQUTN01_09-16-2008.bak 1
2008-09-16 03:21:20.86 backup BACKUP failed to complete the command Exec DB_BACKUP_AQSSQL2 'CFQUTN01'

Have you tried a straight backup to the network share area using query analyzer? Also, you should remove admin share from path (e.g. d$) and ensure the "Backup" folder is shared.

David O Hall of Fame Points: 3228 More actions · Answer 4

I agree with Phil's recommendations although I haven't had time to review the code but a quick scan says it is something I'd change.

The log tells me your getting nightly full backups which is good. My original thought ,that your backup routine was fundamentally broken isn't valid.

You get a this failure:

2008-09-16 03:21:20.86 spid59 BackupMedium::ReportIoError: write failure on backup device '\\advfuji\d$\Backup\AQSSQL2\CFQUTN01\CFQUTN01_09-16-2008.bak'. Operating system error 112(There is not enough space on the disk.). 0

2008-09-16 03:21:20.86 spid59 Internal I/O request 0x4AD61BC0: Op: Write, pBuffer: 0x081C0000, Size: 983040, Position: 13327474176, UMS: Internal: 0x0, InternalHigh: 0xF0000, Offset: 0x1A611E00, OffsetHigh: 0x3, m_buf: 0x081C0000, m_len: 983040, m_actu 0

alBytes: 0, m_errcode: 112, BackupFile: \\advfuji\d$\Backup\AQSSQL2\CFQUTN01\CFQUTN01_09-16-2008.bak 1

2008-09-16 03:21:20.86 backup BACKUP failed to complete the command

But then you get this success an hour later.

2008-09-16 04:16:38.95 backup Database backed up: Database: CFQUTN01, creation date(time): 2006/01/27(08:47:17), pages dumped: 1673645, first LSN: 61036:51:7, last LSN: 61036:65:1, number of dump devices: 1,

So the job is retry and working. At this point I would have to take the error message at face value. At 3:21 AM there isn't enough disk, an hour later there is. You need to investigate what is changing in that hour. The problem seems to be on the destination side.

Hope this helps.

David

madhu-686862 SSChampion Points: 10707 More actions · Answer 5

I tried the direct full backup of the failed database to \\advfuji\d$\backup. Its working fine...

According to David the job is retrying to take the full backup of failed database at next run. So even the full backup of database is failing at 3:21am,its getting backuped at 4:21am. So I have the full backups of all databases..is that right?

David O Hall of Fame Points: 3228 More actions · Answer 6

Madhu

My advice is to become more familiar with your errorlog. All of your backups (log, differential and full) are recorded there. Additionally, the log entries in my previous post shows that full backup succeeded at the 4:21 AM so you are good in regard to that issue.

From what I'm seeing you don't have an immediate problem. I getting the impression that you don't fully understand your backup process and only have a general understanding of backups. I would encourage you to:

1. Review your backup procedures. You need to understand from it fully and completely.

2. You need to test your restores. If you can 't get the backup file off disk and restore to a test environment you'll never be able to put production back if you have data loss. That'll cost you your job in most companies.

3. Kimberly Tripp (sqlskills.com) has some great stuff on backup and recovery. I would find her articles, webcasts and labs and study them.

I hope this helps and good luck