SQL 2005 Backup Failing [UNRESOLVED]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL 2005 Backup Failing [UNRESOLVED]

Author

Topic

gregg1ep00
Starting Member

10 Posts

Posted - 2007-02-07 : 17:54:15

Environment: SQL 2005 Workgroup Ed. (part of SBS 2003 R2 Premium)

I have a database attached to SQL 2005 that is on my D: drive. I'm trying to run a full backup of this database to a backup device also on D:. The first time I ran the backup it worked fine, then I installed SQL 2005 SP1 and now it's failing. The error message is:

quote:

Backup failed for Server 'MYSERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Write on "My Backup Device(D:\Backup\MyBackupDevice.bak)" failed: 112(There is not enough space on the disk.)(Microsoft.SqlServer.Smo)

In the event viewer, the following events are logged in the Application log:

quote:

Source: MSSQLSERVER
Category: (6)
Event ID: 3041
Description:
BACKUP failed to complete the command BACKUP DATABASE MyDatabase. Check the backup application log for detailed messages.

quote:

Source: MSSQLSERVER
Category: (2)
Event ID: 18210
Description:
BackupMedium::ReportIoError: write failure on backup device 'D:\Backup\MyBackupDevice.bak'. Operating system error 112 (There is not enough space on the disk.).

If I do the backup to the MSSQL default backup directory (on C: ), the backup completes successfully. If I try to back up the master database (on C: ) to the backup directory I created on D:, the backup completes successfully. If I try to back up the master database to the MSSQL default backup directory (on C: ), the backup completes successfully.

The SqlServer and SqlAgent processes are both running under a domain user account, and that account has full control of my backup directory on D:. I should also mention that my D: drive has over 100GB free, and the database is only 330MB. It is a simple database model.

I've searched all day today trying to find the solution to this issue, and I can't find anything relevant. Could someone please help me!!! I'm about at my wit's end!

Thanks in advance,
Greg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-07 : 18:08:40

Please post the command that you are using.

Also, please post the output of the following queries:

EXEC master..xp_fixeddrives
EXEC sp_databases

Tara Kizer

gregg1ep00
Starting Member

10 Posts

Posted - 2007-02-07 : 18:27:53

quote:
Originally posted by tkizer

Please post the command that you are using.

Hey Tara, thanks for the super quick response!

The command generated by the SQL Management Studio is:


BACKUP DATABASE [MyDatabase] TO  [MyDatabase Tuesday] WITH NOFORMAT, INIT,  NAME = N'MyDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MyDatabase' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MyDatabase' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MyDatabase'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  [MyDatabase Tuesday] WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

quote:

Also, please post the output of the following queries:

EXEC master..xp_fixeddrives
EXEC sp_databases

EXEC master..xp_fixeddrives results:
drive MB free
--------------------
C 60373
D 108682

EXEC sp_databases results:
DATABASE_NAME DATABASE_SIZE REMARKS
---------------------------------------
MyDatabase 344568 NULL
master 4608 NULL
model 1728 NULL
msdb 5824 NULL
tempdb 8704 NULL

Thanks again!
Greg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-07 : 18:31:27

Try running this command instead:

BACKUP DATABASE MyDatabase
TO DISK = 'D:\SomePath\MyDatabase.BAK
WITH INIT, STATS = 10
GO

If that works, then I believe there's a problem with your backup device.

Tara Kizer

gregg1ep00
Starting Member

10 Posts

Posted - 2007-02-07 : 18:33:44

quote:
Originally posted by tkizer

Try running this command instead:

BACKUP DATABASE MyDatabase
TO DISK = 'D:\SomePath\MyDatabase.BAK
WITH INIT, STATS = 10
GO

Same problem. Here is the exact error:

quote:

Msg 3202, Level 16, State 2, Line 1
Write on "D:\SomePath\MyDatabase Test.bak" failed: 112(There is not enough space on the disk.)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-07 : 18:37:11

Is it possible that D:\Backup is a mount point rather than just a regular folder? If so, mount points have their own free disk space apart from what the drive has.

Tara Kizer

gregg1ep00
Starting Member

10 Posts

Posted - 2007-02-07 : 18:38:24

quote:
Originally posted by tkizer

Is it possible that D:\Backup is a mount point rather than just a regular folder? If so, mount points have their own free disk space apart from what the drive has.

Tara Kizer

The backup directory is a folder on the local system, not a mount point. I am sharing the folder, but the SQL server accesses it directly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-07 : 21:03:13

I'm out of ideas.

Backups were fine for us on service pack 1.

Tara Kizer

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-02-08 : 09:54:19

Two ideas: try backing to a network share like \\servername\sharename and see. Is there a quota system for disks as a part of your login script?

Also, did you try backing up using GUI or Script? if one, try the other.

gregg1ep00
Starting Member

10 Posts

Posted - 2007-02-08 : 10:04:05

Tara, thanks for your help. Hopefully we'll figure this out!

pareshmotiwala, I haven't tried to backup to a network share, but there are definitely no quotas on the file system or anything like that. I have tried the backup job from the GUI, from script, and from the Sql Agent, all with no luck.

Let me try backing up to a network share. I'm pretty sure it's going to work though, since I can apparently backup the DB to any drive that it's NOT attached from.

This problem is bizarre. I read something somewhere about DB snapshots, and that somehow the snapshot and the DB can be competing for disk space or something like that. Does that sound like a plausible explanation? If so, how do I disable DB snapshots for a particular user DB?

gregg1ep00
Starting Member

10 Posts

Posted - 2007-02-08 : 10:21:55

I was just able to perform a full DB backup to my backup folder on D:. I'm trying to setup 5 different backup devices -- one for each day. I can successfully backup once, then when I try to backup to a different device in the same folder, I get the error message.

Any more ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-08 : 11:13:57

My advice is to not use backup devices. Just backup to a file. I haven't used backup devices since SQL Server 6.5!

Here is an old version of the stored procedure I use to backup my database:

http://weblogs.sqlteam.com/tarad/archive/2004/08/04/1876.aspx

I do have an updated version that I just haven't posted yet. This one should work though on 2005. My updated version provides more features and better support for 2005 though.

Each backup will be put inside its own file. The file will be timestamped. Just pass 7 to the retention variable if you want to keep 7 days of backups on disk.

Tara Kizer

gregg1ep00
Starting Member

10 Posts

Posted - 2007-02-08 : 13:32:37

Unfortunately, the backup fails regardless if it's a file or a backup device.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-08 : 13:39:08

Yes but my last post is just saying not to use backup devices. So once you get this error resolved, just use files. Backup devices are considered old school at this point.

Tara Kizer

gregg1ep00
Starting Member

10 Posts

Posted - 2007-02-08 : 14:21:48

Gotcha. Thanks for the tip! ;)

gregg1ep00
Starting Member

10 Posts

Posted - 2007-02-09 : 08:57:42

Still having issues. Does anyone have any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-09 : 13:33:10

I'd suggest changing the subject of this thread to "SQL 2005 Backup Failing [NOT RESOLVED]". This will alert other members here that your problem has not been resolved. I for one often ignore threads that have lots of posts in them already as I assume the problem has been resolved with that many posts.

Tara Kizer

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-02-09 : 15:41:46

If there is a file there from the old backup, try renaming it, or backing up the database to a new filename. The only thing I can think of is if the backup file or the folder was somehow set to read-only.

gregg1ep00
Starting Member

10 Posts

Posted - 2007-02-09 : 17:32:30

quote:
Originally posted by mcrowley

If there is a file there from the old backup, try renaming it, or backing up the database to a new filename. The only thing I can think of is if the backup file or the folder was somehow set to read-only.

The folder isn't read-only. Let me clarify the problem a little.

I want to have 5 different backup files, one for each weekday. My naming scheme is MyDatabase_Monday.BAK, MyDatabase_Tuesday.BAK, etc. I can complete the Monday backup and the Tuesday backup. The Wednesday backup fails. Each backup file is around 330MB. I have over 100GB free on this drive. Once I have 2 full DB backups on D:, I can no longer complete any more backups successfully (no matter what folder I choose to place the backups in). It SEEMS like a disk space issue, but I assure you it is not. I can post screenshots to prove that I'm not losing my mind...

Currently I'm getting around this issue by backing up to a network folder share, and that is working properly. But I'd still really like to resolve this issue, #1 because it's annoying, and #2 because I'd like the backups to live on D:.

Rhapsody
Starting Member

2 Posts

Posted - 2008-11-25 : 11:59:20

Greg, Was this ever resolved?

I am getting the same error.

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 11/25/2008
Time: 1:02:10 AM

Description:
18210 :
BackupMedium::ReportIoError: write failure on backup device 'F:\MYDB\Prod Backup.BAK'. Operating system error 112(There is not enough space on the disk.).

Thanks,
Kamil

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-25 : 13:38:20

Donot Hijack other thread?? Show output of
Xp_fixeddrives
and size of database you are trying to backup.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources