October 24, 2007 at 4:55 pm
Hi,
I have just moved my system databases from D drive to E drive, using the following steps:
http://msdn2.microsoft.com/en-us/library/ms345408.aspx
but it doesn't document how to change the backup devices to the new drive.
Is there a table/s in msdb that I can change?
thanks.
October 25, 2007 at 4:03 am
The location for Backup Devices is stored in the sysdevices table in master. But instead of updating this table you better recreate them in the new location.
If you like to change the default backup location, that's in a registry key. You can change with the following command:
EXEC master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', REG_SZ,'E:\Microsoft SQL Server\MSSQL\BACKUP\'
Markus
[font="Verdana"]Markus Bohse[/font]
October 25, 2007 at 4:13 am
R u a system admin guy? if so, go & change the settings of the registry as per what MarkusB
as suggested. Ensure you have the back up of all the objects...
October 26, 2007 at 11:03 am
... well it depends ... the registry update shown will work if you are using 'default' backups area in SQL Server Maintenance Plans. However if you are not ... then you have some analytical work to do ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 28, 2007 at 3:33 pm
thanks for the replies.
I decided to create new backup devices, instead of changing the existing ones.....seemed to be a lot safer.
October 29, 2007 at 7:03 am
Just make sure that you also grant security permissions to the new location for your backups/database location. The security setting is for SQLServer2005MSSQLUser$MACHINENAME$ SQLINSTANCE to have modify access to this location. Otherwise your backups/restore/attaching may fail. For the purposes of this I used a SQLExpress install to point out the security needs.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply