July 14, 2005 at 8:52 am
I have a database that won't back up to a dump device in which the network path doesn't exist yet. Our software first creates the dump device using a path with a folder created with the current date-time so it is unique. The error is happening for one of clients running our software where the dump device is using a network drive. It works for any of the new databases that were created recently. All the databases are on SQL Server 2000. The backup works if I create the dump device to a known path (path that exists already). But for the other databases it works just fine the other way. The backup is called through the software and can't be changed right now for a simple fix. The backup process backs up the SQL part and other important data files.
The sample commands being called (It errors directly in Query Analyzer).
exec sp_addumpdevice N'disk', N'AFW02_20050622155554', N'F:\DATA\Backup\AFW02_20050622155554\AFW02_20050622155554.bak'
BACKUP DATABASE [AFW02] TO [AFW02_20050622155554] WITH NOINIT , NOUNLOAD , NAME = N'AFW02_20050622155554', NOSKIP , STATS = 10, DESCRIPTION = N'AFW02_20050622155554', NOFORMAT
The error is:
3201 16 0 Cannot open backup device '%ls'. Device error or device off-line. See the SQL Server error log for more details.
It is a networked drive and the folder AFW02_20050622155554 doesn't exist yet. It is actually created when backuping a database to the dump device.
Again it works just fine for any of the newer created databases. And it works even to the network drive if I just change the backup path in the dump device to the existing Backup folder. The problem is I need to get it to work the existing way that it works currently in the software. It works fine for the other databases.
Any ideas?
Thanks,
Daniel
July 18, 2005 at 8:00 am
This was removed by the editor as SPAM
July 19, 2005 at 11:59 am
Any folders have to already exist. The final file can be created by SQL Server during the backup, but the folder itself must be there BEFORE the backup begins.
-SQLBill
July 19, 2005 at 1:43 pm
SQLBill,
The folder doesn't have to exist when creating the dump device. That is the way it has always worked and is working for the other client databases. It is pretty easy to replicate if you create a dump device to a folder that doesn't exist yet. And then backup a database to it. I just can't figure out why for this one database, it doesn't work.
Any ideas are welcome.
Thanks.
Daniel
December 5, 2008 at 5:48 pm
I would also like to know why it doesn't work for one database.
I have a job that backs up all the dbs to an existing folder.
I never created the dumpdevice before for any of them. They all worked fine except this one.
This one gave the error:
No entry in sysdevices for backup device *device*. Update sysdevices and rerun statement.
So I followed some advice and created the device first. Then I get the error Cannot open backup device *devicename*. Device error or device off-line.
ALL the other backup commands that are exactly the same worked and have run fine. Why just this db? It's name is fairly long, is there a restriction? HELP please!
Thanks!
December 5, 2008 at 6:20 pm
What's the db name?
Do you use brackets around it (just to make sure to avoid any unforseen bugs)?
December 5, 2008 at 7:00 pm
BACKUP DATABASE [InterRaiLTCMDSAssessment] TO [InterRaiLTCMDSAssessment_Backup] WITH NOUNLOAD, STATS = 10, INIT , NOSKIP, NAME = 'InterRaiLTCMDSAssessment'
above is the command I am trying to run, this runs 1st time and every time for every other db on the list!
this was the message:
/*
Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'InterRaiLTCMDAssessment_Backup'. Update sysdevices and rerun statement.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
*/
This is what I tried: (though since the others worked it did not make sense to me)
sp_addumpdevice 'disk', 'InterRaiLTCMDSAssessment_Backup', 'R:\MSSQL\BACKUP'
This is the new message:
As you can see there are no spaces or special char's in anything so really brackets are not required.
/*
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'InterRaiLTCMDSAssessment_Backup'. Device error or device off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
*/
Thanks
December 6, 2008 at 1:22 am
Does the directory 'R:\MSSQL\BACKUP' exist?
Is there anything more useful in the SQL error log
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2008 at 9:25 am
Yes the folder existed, all other backups were going there.
Well the backup started working. Possibly because of adding the backup set, though after I did that I tried to manually run the command and it wouldn't let me.
Thanks for stopping by to try to assist.
Thanks!
December 8, 2008 at 12:09 pm
Okay, strangely the log said the db was successfully backed up but the job still said failed.
Just for completion of this thread, I did the following:
sp_dropdevice 'InterRaiLTCMDSAssessment_Backup'
sp_addumpdevice 'disk', 'InterRaiLTCMDSAssessment_Backup', 'R:\MSSQL\BACKUP\InterRaiLTCMDSAssessment_Backup.BAK'
Adding the filename not just the folder name fixed my issue.
Tough being transformed from DB Data Warehouse Architect to Admin!!!! I'm learning fast!
December 8, 2008 at 12:26 pm
Thanks for the update and glad it worked.
As an FYI, I don't recommend dump devices and would just back up to disk files. I'd use a different file name every night as well.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply