| Author |
Topic |
|
tdiboll
Starting Member
2 Posts |
 Posted - 2004-08-16 : 10:50:38
|
| I am running Windows 2000 Professional.I am trying to do a sql backup through enterprise manager. This is the error message that I am receiving:Cannot open backup drive D:\SQL Backups\8.10.04. Device error or device offline. See Sql Server error log for more details. Verify database terminating abnormally. This is the message in my SQL logs: 3041: Backup failed to complete the command backup database (Commerce Center Production) to disk = N'D:Sql Backups\8.10.04' with NOINIT, NOUNLOAD, Name = N'Commerce Center Production backup', NOSKIP, STATS =10, NO FORMAT 18204: Backup Disk File::Open Media:Backup device D:\Sql Backups\8.10.04 failed to open. Operating System Error = 2. (The system cannot find the file specified). I have consulted an outside source. Following is a list of areas he has explored from a response he received from Microsoft: Reapplied SQL Sp 3 Applied Windows 2000 Sp4 Applied numerous hot fixes Ran multiple scripts to attempt to isolate the problem Ran maintenance on the database Short of starting with a clean slate and rebuilding SQL and redoing P21 Maintenance jobs, has anyone ever run across this or have a clue how do solve without redoing SQL. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-16 : 11:52:49
|
| Try a backup from query analysersomething likebackup database mydb to disk = 'd:\mydb.bak'see what that says.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-16 : 13:11:06
|
| Are you running these backups from a Maintenance Plan or from a job? Can you post the script if you're doing it from script or job.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-17 : 06:46:50
|
| I've had some grief trying to use paths with spaces in them and BACKUP/RESTORE commands."D:\Sql Backups\8.10.04" looks pretty much like a folder name to me, in which case BACKUP would be exepecting some sort of "\BackupFileName.BAK" to be appended to it(The "Name" parameter is NOT the filename, but rather a descriptive name for the backup)Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-17 : 07:21:44
|
| I was thinking it could be some kind of a backup device. ??? Why would someone ever name a directory or file that? Yuck! It's obviously a date. I always use the format 20040810. Much, much less problems. Besides that, it saved my life in 2000. lolMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
tdiboll
Starting Member
2 Posts |
Posted - 2004-08-18 : 15:34:33
|
I was able to backup my database from query. I am still unsure why I cannot do this through Enterprise Manager. I have a weekend scheduled so that my SQL can be rebuilt. In the meantime, do you know how I can restore a backup through QA.Thanks,Traceyquote:
Originally posted by nr
Try a backup from query analysersomething likebackup database mydb to disk = 'd:\mydb.bak'see what that says.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
MuadDBA
628 Posts |
Posted - 2004-08-18 : 15:38:54
|
quote:
Originally posted by tdiboll
I was able to backup my database from query. I am still unsure why I cannot do this through Enterprise Manager. I have a weekend scheduled so that my SQL can be rebuilt. In the meantime, do you know how I can restore a backup through QA.Thanks,Tracey
Try looking upt he RESTORE command in BOL. It's pretty straightforward. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 16:38:47
|
| Not sure I would agree with you crazyjoe ... you have to interrogate the backup file to find out where it would have liked to be restored to, and what logical names it wants to use, had it have been restored to the orignal machine, and then tell it some of those plus the actual names & locations you'd like it to be restore to ...And then, if you are restoring to a different server, you have to re-synchronise all the database users to the Login IDs on the new machine.And if it was using multiple files for backup, or for the database and its logs, the job just gets harder. Assuming the database wasn't replicated. And it isn't in use when you try to restore. And you know the password for the backup files. And there's an R in the month ...I wrote an SProc to take care of all these things, but its still a long way from being able to handle all the variations I've stumbled over along the DBA road ...Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-18 : 17:44:27
|
| This does an automatic copy and restore of a backup - setting the file locations.http://www.nigelrivett.net/s_TestRestore.htmlI use it for testing the backups - just restores a few backups a day (depending on how long they take) from a list of locations and databases. Eventually gets round to all the backups.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Backup Failure in Enterprise Manager