Local Disk Backup Fails

Question

Local Disk Backup Fails

John Walters

Grasshopper

Points: 15
More actions
May 6, 2005 at 1:26 pm

#109577

We have a 1.3tb SQL Server 2k db on a SAN via Fiber Channel. We have a mixed windows-unix environment, so said server also has access to several very large arrays via samba. Initially, we only had 1 SAN array hooked directly to the server, in which the db resided, but recently we received another one, for backup purposes.
Right now, I have 6 different dbs, the big one (1.3tb), a small one (40 gb) and a medium one (250 gb). About 3 months ago, we began a daily backup of the small, mission critical database through the SAMBA network connection via a 'backup device'. We were able, without an issue, to also back up the medium sized db to that disk via the network.
However, we attempted to back up the very large db to the local disk, without success. SQL Server would create the file, 0kb in size, on the drive, but it (after several hours) never began populating. We then tried to back up the small and medium dbs to this device, with the same exact problems. The file specified in the device would be created, but only at 0kb in length.
Initially, we thought it to be a permissions issue, and gave the drive write access by any and everybody. This did not solve the problem.
On creating a Windows share, the backup device writes correctly. But, when it is 'local', it never populates.
Any help would be appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Chris Scarff SSC Rookie Points: 39 More actions · Answer 1

Basically, John's saying, we were unable to backup to a LOCAL hard drive (Y:\ drive). Even though we had full right access to the file system.

Now, we CAN write to it if we create a SHARE on that same drive and backup to the UNC path (\\machinename\SQLBackups\ )

That works perfectly... BUT WHY?

Anyone?

Oh, we're not doing anything fancy, just doing a simple backup within the SQL Server 2000 Enterprise Manager.

Thanks,

Chris

Jason Hall Hall of Fame Points: 3906 More actions · Answer 2

Are you sure that the backup is not writing? In windows 2000 if you are viewing the backup file in windows explorer you will not see the filesize grow untill the backup is completed. It goes from 0 GB to the final size. If you want to see the progress of the backup turn statistics on and you will be able to see its progress.

What makes you think that the backup is failing even though you do not see an error message?

Chris Scarff SSC Rookie Points: 39 More actions · Answer 3

I wish it were that simple. We no nothing was happening when the file size did not grow, EVER, and there was no CPU ulitization. We let it attempt to backup for quite some time.

When we used the shared UNC path of the same drive, the backup file IMMEDIATELY grew in size and CPU utilization was "kicking".

ThanNhi Nguyen SSC Enthusiast Points: 152 More actions · Answer 4

I created backup plan (using Enterprise Manager) for several servers, some of them worked some of them didn't . For those didn't do the backup, the error I got was "failed to do the backup". How could I check to see why the plan failed? On the same plan, I had the transaction log backup also, the transaction log got backup but not the database. Could someone please tell me why?

Thanks,

tn

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 5

To answer one part of your question you can check to see what is failing in a maintenance plan by enabling the report writing option (just specfiy what you want the file to be called and where you want it to be put, oh yeah, and click on the checkbox to enable it).

You'll then have a text file to look at which will tell you what failed and when (unfortuantly it will also tell what worked which will bulk it out a fair amount but it's better than not having anything).

As for your second question, are you sure you mean the transaction log backup is working but the DB one isnt? I only ask because it's normally the other way around (mainly because the DB is set in something other than FULL recovery mode).

I've seen problems with maintenance plans before when disk space is too tight (and sometimes when event logs are full) but if both of these are ok then I'll let someone else offer an opinion.

Hope this helps

ThanNhi Nguyen SSC Enthusiast Points: 152 More actions · Answer 6

Mike,

Thanks for the tips. The dbs and trans. logs get backup ok now.

When the transaction log files get backup, don't they suppose to delete the old inactive transactions? Or do I have to setup some options? If I have to setup options, then what & where? We have some HUGE transaction log files.

Thanks,

tn