Flat File Backup Failed *suddenly*

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Patrick (patl_at_reply.newsgroup.msn.com)
Date: 08/23/04 

Date: Mon, 23 Aug 2004 14:42:29 +0100

OS: Win2K Server SP4
DB: SQL Server 2000 SP3a

I have a database that has a1.1GB LDF file and a 380MB MDF file

I have a database maintenanec plan set up to do full database backup (i.e.
not differential) of all non-system databases including this database in
question.

The backup to d:\MSSQL\BACKUP (with a sub directory for each DB) has been
working fine since the server was set up 6 months ago. Suddently, the
backup stopped working on Saturday (the Friday backup is successful and no
one was in the office on Saturday, to rule out any system changes that could
have trigger this).

Observations
*1) The error logged in the error log file is :
2004-08-21 21:25:14.07 spid60 BackupDiskFile::OpenMedia: Backup device
'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408212125.BAK' failed to open.
Operating system error = 32(error not found).
2004-08-21 21:25:14.20 backup BACKUP failed to complete the command
BACKUP DATABASE [YJBLive] TO DISK =
N'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408212125.BAK' WITH INIT , NOUNLOAD
, NOSKIP , STATS = 10, NOFORMAT

*2) If I change the job to just do the backup without verify, then the job
completes, but
*2.1) Normally, the BAK file used to be 3xxMB large, but now the BAK file
which only succeeded in backing up without verifying is 1xxMB large

*3) The Job History indicates
Executed as user: WEBSERVER\SQLServer. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
The job failed. The Job was invoked by Schedule 2 (Schedule 1). The last
step to run was step 1 (Step 1).

*4) The backup file appear corrupted:
*4.1) If I look at the Maintenance Plan history, I found:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'd:\MSSQL\BACKUP\DBLive\DBLive_db_200408231407.BAK'. Device error or device
off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
*4.2) If I try to restore this (corrupted) 1xxMB BAK file to another DB, it
doesn't work. After the popup to say restore has completed, Enterprise
Manager shows the database as grey with "(Loading)" appended to the database
name (even after 5mins)

*) The user group Everyone has full permission on d:\MSSQL\BACKUP

SOS !! Why is my backup suddenly failing and how could I resolve this?
Note it is only this database whose backup are failing since Saturday (e.g.
I have a DB which has a 3.8GB LDF file which backup with no problems!)

Relevant Pages

  • RE: ESENT Logging
    ... SBS server 2003 has a backup job that will ... 145881 How to Use Jetpack.exe to Compact a WINS or DHCP Database ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: Copying a database between servers
    ... In 2000 you can backup to an UNC path ... or a local drive and the same goes for the restore. ... backup the database and then restore it on the new server. ... >>> one SQL Server 7.0 database with 'select' access to another SQL ...
    (microsoft.public.sqlserver.tools)
  • RE: Adventures in Server restore
    ... SP1 for exchange change the db jet and should fix this problem ... > I thought I'd pass on my experience in bringing back an exchange server ... I'm not sure what happened (but a online backup was going on) ... > persists then please restore the database from a previous backup. ...
    (microsoft.public.exchange.admin)
  • RE: How do I restore from mdf and ldf files?
    ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
    (microsoft.public.sharepoint.portalserver)