Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Database backup problems

Status
Not open for further replies.
c00ld00d

c00ld00d

MIS
Jul 21, 2003
13
US
I have a SQL Server 7.0 server with 2 production databases on them. For the last several weeks I have been having trouble getting the two dbs to backup. This week, one of them seems to be backing up just fine. Here's the details:

I have a separate maintenance plan for each of the databases. Both get a full backup with verify each night. One is scheduled at 2 AM the other at 2:15. The db that backs up at 2 AM takes about 5 minutes to back up.

I'm backing up to disk files. I am not backing up to a network share.

After the job executes, the log shows

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'BSD_TRANS_S2' as 'BSD_CO\mullinsbr' (trusted)
Starting maintenance plan 'FA_DB Maintenance' on Thu May 20 11:01:19 2004

[1] Database FA_DB1_31SP1: Check Data and Index Linkage...

** Execution Time: 0 hrs, 0 mins, 1 secs **

[2] Database FA_DB1_31SP1: Database Backup...
Destination: [F:\SQLData\BACKUP\FA_DB1_31SP1\FA_DB1_31SP1_db_200405201101.BAK]

** Execution Time: 0 hrs, 0 mins, 4 secs **

[3] Database FA_DB1_31SP1: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'F:\SQLData\BACKUP\FA_DB1_31SP1\FA_DB1_31SP1_db_200405201101.BAK'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
Deleting old text reports... 1 file(s) deleted.

End of maintenance plan 'FA_DB Maintenance' on Thu May 20 11:01:24 2004

SQLMAINT.EXE Process Exit Code: 1 (Failed)



Backups of the log files also fail with a similar message. I've checked that the login used for the SQLServerAgent has the correct permissions. I've looked all over these forums, and it seems the only other problems like this have been where backups are being done to a network share. All of the fixes for that that I have tried have not been successful.

I'm too young to be loosing my hair this fast. Can someone give me some info to fix this so I can stop pulling it out?


Thanks,
Brian

 
Sort by date Sort by votes
Is 'F:\SQLData\BACKUP\' local or on another server?

Thanks

J. Kusch
 
Upvote 0 Downvote
Still having the same problem. It seems to be the verify of the backup that isn't working. The Varify step shows this error...

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'F:\SQLData\BACKUP\FA_DB1_31SP1\FA_DB1_31SP1_db_200402210200.BAK'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.


I'm getting nervous. Anybody have ANY suggestions?

Thanks,

Brian
 
Upvote 0 Downvote
Is the backup file FA_DB1_31SP1_db_200402210200.BAK in the 'F:\SQLData\BACKUP\ folder or the 'F:\SQLData\BACKUP\FA_DB1_31SP1 folder???

Can you also post the error message found in the SQL Server error log ... "(Device error or device off-line. See the SQL Server error log for more details."




Thanks

J. Kusch
 
Upvote 0 Downvote
After the job cancels there is no file. During the job the file is created, but then is deleted when that step fails.

Here's the SQL Server Error Log:

2004-06-02 02:16:30.06 kernel BackupDiskFile::OpenMedia: Backup device 'F:\SQLData\BACKUP\FA_DB1_31SP1\FA_DB1_31SP1_db_200406020216.BAK' failed to open. Operating system error = 2(The system cannot find the file specified.).
2004-06-02 02:30:14.60 kernel BackupDiskFile::OpenMedia: Backup device 'F:\SQLData\BACKUP\FA_DB1_31SP1\FA_DB1_31SP1_tlog_200406020230.TRN' failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.).


My tape backups run at midnight, so there should be no conflict there. All users are logged out of the DB by 11:00 PM the night before, so I'm not convinced that there is any conflict there, either.

It'd sure be nice if the error log would tell you which process it thinks is using the file...

Thanks,

Brian
 
Upvote 0 Downvote
I am a bit puzzled on the run times of the first 2 step:

1] Database FA_DB1_31SP1: Check Data and Index Linkage...

** Execution Time: 0 hrs, 0 mins, 1 secs **

[2] Database FA_DB1_31SP1: Database Backup...
Destination: [F:\SQLData\BACKUP\FA_DB1_31SP1\FA_DB1_31SP1_db_200405201101.BAK]

** Execution Time: 0 hrs, 0 mins, 4 secs **

Step 1 took 1 second and step 2 took only 4 seconds. IS there any data in this DB because 4 seconds seems awefully fast.

Lets give this a try ... open your MaintPlan and uncheck the verify and TLog backup for the moment. See if it runs.

If it does ... turn the verify back on and try it again.

Let us know what happens.

Thanks

J. Kusch
 
Upvote 0 Downvote
It is a small database, only about 35MB.

I did what you suggested and the backup succeeds. The weird thing is that the file is nowhere to be found...

I've tried changing the folder its backing up to, but that doesn't seem to help. The file is still gone...

Thanks,

Brian
 
Upvote 0 Downvote
Have you tried recreating the MaintPlan from scratch. Keep the existing one and see if you can create another one JUST with the backup of the DB. No other options like IntegCheck or TLog backup.

Thanks

J. Kusch
 
Upvote 0 Downvote
Something else weird I just noticed. The properties on the database (in EM, clicking on the DB) shows the last backup done was a manual one I did on 6/2.

Thanks,

Brian.
 
Upvote 0 Downvote
That was going to be my next question ... if you had done a manual backup to test things out. Just for fun, lets do this.
Code: 
USE master

EXEC sp_addumpdevice 'disk', 'FA_DB1_31SP1_1', 
   DISK =F:\SQLData\BACKUP\FA_DB1_31SP1\FA_DB1_31SP.BAK'

BACKUP DATABASE FA_DB1_31SP1 TO FA_DB1_31SP1_1

As many will agree with me, you should create your own backup jobs and schedule them. Try to stay away from MaintPlans if at all possible. You could create a job with the code listed above. You can also create other steps w/in the job to verify the DB Backup. You may also want to create another Job to take backups of your TLogs too. Jobs can also be created for every task that you can find in a MaintPlan plus they are a bit easier to deal with and account for than MaintPlans.

Thanks

J. Kusch
 
Upvote 0 Downvote
Sorry ... missed a tick (')
Code: 
USE master

EXEC sp_addumpdevice 'disk', 'FA_DB1_31SP1_1', 
   DISK ='F:\SQLData\BACKUP\FA_DB1_31SP1\FA_DB1_31SP.BAK'

BACKUP DATABASE FA_DB1_31SP1 TO FA_DB1_31SP1_1


Thanks

J. Kusch
 
Upvote 0 Downvote
These are the same symptoms I had when trying to backup the db to our snaps server. After I created a mapped drive to that server, the backup worked when ran as a scheduled job.

I know you said it is a local backup in your case.

John
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

CodeWell
  • Locked
  • Question
SQL Server 2017 Install Issue - Database Not in Configuration Manager
Replies
0
Views
401
CodeWell
CodeWell
baran121
  • Locked
  • Question
take a database from system databse place
Replies
2
Views
340
iambob
iambob
bethabernathy
  • Locked
  • Question
Front End Access 365 Database dsn Connection Fails when attempting to link to 2016 SQL server
Replies
1
Views
323
dhookom
dhookom
torquinian
  • Locked
  • Question
Error 0x80131904 when trying to connect to database
Replies
0
Views
327
torquinian
torquinian
RRinTetons
  • Locked
  • Question
Logging into a database using SSMS from non-domain machine
Replies
1
Views
390
fredericofonseca
fredericofonseca

Part and Inventory Search

Sponsor

Back
Top