May 30, 2003 at 10:35 am
What do I need to do in order to update the sysdevices table? The message that I am getting is "Ad Hoc updates to system catalogs are not enabled." What configurations do I need to do in order to update the table? I have created several backup devices with the wrong name and need to change the name and location. Thanks.
May 30, 2003 at 11:06 am
Okay, first I'll tell you how to do it, but then I'll also ask the why.
In order to allow ad hoc updates, you'll have to issue an sp_configure command along with a RECONFIGURE with override. Here's how:
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
When you are done, you set allow updates to 0 instead of 1.
Now as to the why... You have at your disposal sp_dropdevice and sp_adddumpdevice. If you know how the settings are going to change, you could script dynamically around these two stored procedures. Also, so far as backups are concerned, BACKUP DATABASE and BACKUP LOG do not require a preconfigured device. You can specify the path in the statement itself.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
May 30, 2003 at 11:27 am
Good question on why. In this case, I am creating the backups on a sperate raid device. This device is a temporary solution until we get another storage device. Then again I will be changing the location only. The device is so that I do not have to modify each job when I change the location of my storage device. If there is a better solution, I am all ears. Thanks.
May 30, 2003 at 12:04 pm
Sure...
Stored procedure where the root path is kept in a string inside the stored procedure. Parameters are passed to flesh out the full T-SQL to execute. Since you're probably executing this under a sysadmin role, you're not worried about the dynamic SQL permissions issue.
Then, when you switch root locations, you alter the stored procedure to change the root path in the string.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply