| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2005-06-21 : 06:54:07
|
| I'm having a similar problem to the last post on this thread:http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=25725But when I call sp_addumpdevice I get error 15061: "Add Device request denied. a physical device named 'c:\MYBackup.dat' already exists"This happens even though, on the line before, I have just succesfully called sp_dropdevice and passed 'DELFILE' in as well.any ideas? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-21 : 06:58:31
|
| I'll ask the obvious question: why drop a backup device if you're just going to recreate it under the same file name? |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-06-21 : 09:21:24
|
| Not that it should matter... but I have a script that is part of step 1 of a series of DTS packages that do the conversion.This is the first step. It established the required structure and does other things to. I've just gone back now to re-run all the dts packages together one after another to see how long it takes. Hence the problem. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-06-21 : 11:01:39
|
| Is it something to do with "updating" SQL Server somehow so that it knows what I have done? Or do I need to make an additional delete from a system table in msdb (eeek). |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-21 : 18:32:41
|
| Are you sure you're dropping the device by the correct logical name? That's the only thing I can think of.I'm not sure what you're doing, but creating backup devices is not something I personally would include in a DTS package. I wouldn't even include backup operations in a DTS package. They're the kind of operations that are out of place with the type of things that DTS was designed to do. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-21 : 18:34:44
|
| Who actually uses backup devices anymore, besides coolerbob? I haven't used them since SQL Server 6.5.[EDIT] noticed my question mark wasn't in the correct place.Tara |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-21 : 18:54:05
|
I haven't used them since 6.5I cringe every time I hear the term SQL Server device.Bob: Did you run sp_helpdevice to verify the device was there before you tried to drop it? Did you run it afterwards to see if it was gone?quote:
Originally posted by tduggan
Who actually uses backup devices anymore?, besides coolerbob I haven't used them since SQL Server 6.5.Tara
CODO ERGO SUM |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-06-21 : 23:06:23
|
| >> I cringe every time I hear the term SQL Server device.I was working in a utilities firm that was an Oracle shop when a sysadmin called all the DBA's over to "help" with something....Picture this...6 Oracle DBA's, a small army of developers and managers crammed around one desk with the 6.5 UI on screen, trying to increase the data file size....EDIT: I suck at English!DavidMA front-end is something that tries to violate a back-end. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-06-22 : 03:37:50
|
quote:
Originally posted by robvolk
I'm not sure what you're doing, but creating backup devices is not something I personally would include in a DTS package. I wouldn't even include backup operations in a DTS package. They're the kind of operations that are out of place with the type of things that DTS was designed to do.
I have broken the data conversion into seperate phases. I backup each of my phases once they are working so that I have a foundation to work on without having to rerun all the scripts everytime I make a change or test something. It has worked fine.As for using devices.... Hey, I'm just following BOL. How do you guys backup a database?? Enlighten me.A. Back up the entire MyNwind databaseNote The MyNwind database is shown for illustration only.This example creates a logical backup device in which a full backup of the MyNwind database is placed.-- Create a logical backup device for the full MyNwind backup.USE masterEXEC sp_addumpdevice 'disk', 'MyNwind_1', DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'-- Back up the full MyNwind database.BACKUP DATABASE MyNwind TO MyNwind_1B. Back up the database and logThis example creates both a full database and log backup. The database is backed up to a logical backup device called MyNwind_2, and then the log is backed up to a logical backup device called MyNwindLog1. Note Creating a logical backup device needs to be done only once.-- Create the backup device for the full MyNwind backup.USE masterEXEC sp_addumpdevice 'disk', 'MyNwind_2', 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_2.dat'--Create the log backup device.USE masterEXEC sp_addumpdevice 'disk', 'MyNwindLog1', 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwindLog1.dat'-- Back up the full MyNwind database.BACKUP DATABASE MyNwind TO MyNwind_2-- Update activity has occurred since the full database backup.-- Back up the log of the MyNwind database.BACKUP LOG MyNwind TO MyNwindLog1 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-22 : 07:12:36
|
| You could do backups with a maintenance plan, or with one of the backup scripts posted on this site.Maintenance plans backup to files with timestamped names, and take care of cleanup of the old files, and also create and schedule the jobs.I believe the backup scripts will give you similar functionality.There is really no point in using backup devices.CODO ERGO SUM |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-22 : 07:24:16
|
quote:
There is really no point in using backup devices...You could do backups with a maintenance plan
I would say there's not much point in using maintenance plans either.  Seriously, I consider them a bad habit best avoided.You can back up directly to a disk file:BACKUP DATABASE myDB TO DISK='C:\backuppath\backupfile.bak' WITH INITIf you want a timestamped filename:DECLARE @filename varchar(255)SET @filename='c:\backuppath\backupfile_' + convert(char(8), getdate(), 112) + '_' + replace(convert(char(8), getdate(), 108), ':', '') + '.bak'BACKUP DATABASE myDB TO DISK=@filename WITH INITI'm pretty annoyed that Books Online doesn't have a BACKUP example using TO DISK, but it's documented anyway. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-06-22 : 10:53:32
|
| Again, excellent advice Robvolk. Thanks! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-22 : 18:11:35
|
I have heard a number of people here say to avoid using using maintenance plans for SQL Server backups, but I am not having any trouble with them.I backup about 400 databases on a daily basis on 30+ servers, and do not have any problem with database maintenance plans.I also use them to run transaction log backups at least every 15 minutes on all production transaction databases, over 20,000 transaction log backups per day across all servers.If I was having even a very small number of problems with maintenance plans on a regular basis, I would have dumped them years ago.I'm starting to think you guys are just crusty old DBAs who can't stand to use a wizard. quote:
Originally posted by robvolkI would say there's not much point in using maintenance plans either. Seriously, I consider them a bad habit best avoided.
CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-22 : 18:15:00
|
quote:
I'm starting to think you guys are just crusty old DBAs who can't stand to use a wizard.
Yep that'd be it. Using a wizard in the field that you specialize in just doesn't seem right. Also, don't you just love the errors that you get from sqlmaint.exe?Tara |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-22 : 18:32:13
|
Well, as I said, I'm not getting errors.Maintenance plans use the extended stored proc master.dbo.xp_sqlmaint, so I don't think I have ever used sqlmaint.exe from the command line or from xp_cmdshell.What kinds of errors have you seen with it?quote:
Originally posted by tduggan...Also, don't you just love the errors that you get from sqlmaint.exe?...
CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-22 : 18:35:41
|
| sqlmaint.exe is used by xp_sqlmaint which is used by the maintenance plans. So you are in fact using it, just indirectly. I've never used it directly either. The error:sqlmaint.exe failed. Gee, thanks for the info. Now how do I troubleshoot it? It's like the Unspecified Error that people get in DTS. How informative!Tara |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-22 : 18:48:36
|
Don't you create a text log file to capture error messages and other output? I mean when you use options like these with xp_sqlmaint:-Rpt "D:\MSSQL\LOG\Backup All System Databases4.txt"-DelTxtRpt 2WEEKSquote:
Originally posted by tduggan
sqlmaint.exe is used by xp_sqlmaint which is used by the maintenance plans. So you are in fact using it, just indirectly. I've never used it directly either. The error:sqlmaint.exe failed. Gee, thanks for the info. Now how do I troubleshoot it? It's like the Unspecified Error that people get in DTS. How informative!Tara
CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-22 : 18:59:17
|
| I think those must be the options that we use when setting up log shipping, which is when we use maintenance plans here as I can't get them to switch to custom log shipping. I much prefer running something that I know exactly what it is doing. That's one of the reasons why I go the custom script way.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-22 : 19:17:50
|
quote:
I much prefer running something that I know exactly what it is doing.
Couldn't agree more, even if they run error-free, you really don't know what they do. And if you run a trace while the plan is executing, you'll find plenty of interesting and completely undocumented stuff going on.And yeah, us crusty old DBA's remember the joy of SQL Server 7.0 RTM's Maintenance Plan Wizard, which would leave databases in single-user mode (and sometimes suspect) after it failed to finish off the reindex task. I think it took until SP2 to fix that. Kinda hard to run a 24/7/365 operation with such a "quirk". |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-22 : 21:37:13
|
| I hope you guys know I was kidding about "crusty old DBAs". I'm fairly certain I'm one of the oldest and crustiest here, and have seen my share of quirks with 7.0, 6.5 and even 6.0.CODO ERGO SUM |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-06-23 : 04:22:54
|
| That's good to know guys. I wont go down that route then. Sounds like you've got some work to do Michael.As for the two legends, what you learned from your custom maintainance scripts?What kind of thing should not be left out?There's backup, re-index (and a host of other DBCC commands), Updatestats, alter db size.Anything else major? |
|
|
|
Next Page
|
sp_addumpdevice