Backup Fails On SQL SERVER 2000 1

[CTekGirl]

Environment: Windows 2000 Server/SQL Server 2000 SP3
Database Name: FSPROD (size 8gb)

I am trying to do an online backup of a Production database and it is not working. I tried at various times in the day and night. It worked before in the past, but now it stops at about 85%. Plenty of disk space. I have included the log information listed below:

When I tried to back up to a file or device I got the following error:

I am open to provide any other information upon request. I would really like to know what is going on.

BACKUP failed to complete the command BACKUP DATABASE [FSPROD] TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\FSPROD' WITH INIT , NOUNLOAD , NAME = N'FSPROD backup', SKIP , STATS = 10, NOFORMAT

Internal I/O request 0x04916C10: Op: Write, pBuffer: 0x0EC10000, Size: 983040, Position: 5645081088, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x50791A00, OffsetHigh: 0x1, m_buf: 0x0EC10000, m_len: 983040, m_actualBytes: 0, m_errcode: 2, BackupFile: D:\Microsoft SQL Server\MSSQL\BACKUP\FSPROD

BackupMedium::ReportIoError: write failure on backup device 'D:\Microsoft SQL Server\MSSQL\BACKUP\FSPROD'. Operating system error 2(The system cannot find the file specified.)

 

[JayKusch]

Are you firing this backup w/in a job or manually?

Thanks

J. Kusch

 

[CTekGirl]

I am trying to start it manually. I watch the progress bar get almost to end and then it fails. I have been reading about conflicts with tape backups, but I have tried to run this backup during the day or early morning when the tape backups are not running. Could this still be an issue? I read that 'open' files can sometimes cause this problem. If so, how can I check this? As I stated before I have be able to run online backups at anytime in the past and it worked fine.

 

[CTekGirl]

Still looking for help on this one.

 

[SQLBill]

Have passwords been changed?

Who is the backup job running as? Does that login have access to the directory/folder?

Does that folder still exist? (recheck the WHOLE path).

 

[TitleistDBA]

Is the datebase on the 'D' drive also?

http://www.acushnetcompany.com

 

[TitleistDBA]

I would try this.
Open SEM (Enterprise Manager)
Drill Down to Management\back.
Create a new backup device on your disk.
(If you have another disk like a 'E' or 'F' drive create it on that instead of 'D'. If you are reading from the same disk you are writting to your backup will take much longer.)
change your backup syntax to look like this.

BACKUP DATABASE [Name] TO [DeviceName] WITH INIT , NOUNLOAD , NAME = N'SomeName', NOSKIP , STATS = 10, DESCRIPTION = N'SomeDesc', NOFORMAT

http://www.acushnetcompany.com

 

[SQLBill]

Are you sure this has worked in the past?

Reason I ask is that you don't have a file listed in the path.

N'D:\Microsoft SQL Server\MSSQL\BACKUP\FSPROD'

I believe it should be like this:

N'D:\Microsoft SQL Server\MSSQL\BACKUP\FSPROD\FSPROD.bak'

Or with some other file name in place of the bold part.

-SQLBill

 

[CTekGirl]

Hopefully I have provided answers to all of the responses.

No password changes.
I am running Enterprise Manager under my Windows ID.
My Windows ID is part of the Administrator group. The database is owned by 'sa'.
Yes the database is on the D Drive
Yes I have run this before but I have deleted and recreated the device during this process that is why the filename is not showing.

This is the hard drive layout:
Windows 2000 O/S C Drive
SQL Server 2000 D Drive
D:\Microsoft SQL Server\MSSQL\PSOFT\db\FSPROD
D:\Microsoft SQL Server\MSSQL\PSOFT\bupDBE:\MSSQL\PSOFT\bupDB
*backup devices have been created on both the D and E drive.

The directories do exist.

I created a new device on the E Drive. When I attempted the backup I get the following error:
BackupDiskFile::CreateMedia: Backup device 'E:\MSSQL\PSOFT\BUP\TESTDB.BAK' failed to create. Operating system error = 5(Access is denied.)

I am still having the same problem on the D Drive with FSPROD.

When I go through explorer using my Windows ID I can write to this directory. Is there a SQL Server ID that needs write access too?

I tried backing up some of the delivered sample databases to the D drive and it works fine. I also tried to backup PSTEST which is a copy of FSPROD and it works fine. The FSPROD database will not allow me to do an online backup.

 

[JacquesJTheron]

Are you sure the disk is errorfree - ie scandisk shows no problems?

 

[TitleistDBA]

What account is your SQL running on? Because this is a PeopleSoft database I guessing that sql is running on a domain account? (We have a very large PS database here. So I know your pain!) Is that domain account locked? or has it been removed from the local admin account on the server? I don't know if this will work but open query analyzer and copy the sytax from you backup job into query analyzer and run it. Make sure you log in as 'sa'.

http://www.acushnetcompany.com

 

[TitleistDBA]

To find out if your SQL server is running on a domain account. Right click on the server name and select properties. Click on the security tab. At the bottom you will see a Start Up Service Account. What account is the server logged in as? If it is system account (which is not a good idea) Go to the server and see who has logged in. That account might not have rights to do what you need. What you should have is a domain account for the SQL server only. Put that domain account in the local admin group for the server. Also make sure the SQL agent is running on the same account. Right click on the SQL server Agent under management and select properties. Under the general tab you will see the start up service account.
Good Luck.

http://www.acushnetcompany.com

 

[JayKusch]

Quick Question ... is there a .bak file in the 'D:\Microsoft SQL Server\MSSQL\BACKUP\FSPROD' directory from a previously SUCCESSFUL backup operation of the database???

IF SO ... delete that copy and retry your backup operation.
We will whip this issue one way or another - lol.

Thanks

J. Kusch

 

[CTekGirl]

I am trying the suggested steps. I will update this post.

In reply to the previous backup, I did delete it and I got the same error.

Question: Why isn't it a good idea to use the system account? Is it possible to use an account created on the local machine(local administrator group) instead of a domain account and get the same results?

 

[CTekGirl]

I am trying the suggested steps. I will update this post.

In reply to the previous backup, I did delete it and I got the same error.

Question: Why isn't it a good idea to us the system account? Is it possible to use an account created on the local machine(local adminstrator group) instead of a domain account and get the same results?

 

[TitleistDBA]

Yes your could create a local admin account and get the same results. I just find it is alot eassier to manage all my SQL accounts if they are Domain accounts. Plus if you are doing anything like log shipping (which is a very good idea for disaster recover for PS databases.) you will find that you must have SQL on a domain account.

http://www.acushnetcompany.com

 

[CTekGirl]

TitleistDBA you were correct in your suggestion. I am uncertain how this property was changed. Perhaps through some 'creative' administration this was changed to LocalSystem without my knowledge.

One additional question, I created a user on the local machine called SQLAdmin and put it in the administrator group. I stopped SQL Server and changed the logons to SQLAdmin and restarted SQL Server. I was still unable to do the backup process, do you know what else I need to do?

I ended up using my own domain account and the backups worked fine. When SQL was installed orginally my account was used to start the services, but it may have been changed.

You mentioned 'log shipping' are you referring to offsite data storage? We currently have the entire server backed up nightly with offsite tape storage. Any additional advice or suggestions would be greatly appreciated.

 

[TitleistDBA]

I'm glad I was able to help. One of the things that we do here for our PeopleSoft Manfacturing database is use SQL log shipping for Disaster Recovery. This is like having a Hot spare. You can look up transactional log shipping in BOL and it will show how to set it up. This is how it works for us. We have another SQL server in one of our other locations. Every 15 minutes we send the transaction log from PROD to the DR server. I can't tell you how valuable this has been for us. Just last week we lost the data array on our production server, and in just 10 minutes we were able to be up and running by changing the ODBC connection on the app server to point to the DR server. We had to use this approach as our PS database is over 280gig. We could never restore from tape. It would take over 20hrs. Another tool we use is SQL LiteSpeed to backup the database. It is able to compress our backup to 25gig! The best part about SQL LiteSpeed is it uses extended stored procs so we don't need a seperate backup plug in. We can just use Enterprise Manager to create and schedule backups. I hope this helps.
Good Luck.

http://www.acushnetcompany.com