February 16, 2008 at 10:07 am
Hi, I have over 200 backup devices which all point to a path like: E:\MSSQL\BACKUP. I recently added a new drive and the letter is F. I want to relocate my backups to this drive but I need to modify my backup devices so they point to F:\MSSQL\BACKUP.
Does anyone have or know of a custom script that I can use to modify each device?
Does anyone know of a system SP that will do it?
I know about sp_adddumpdevice and sp_dropdevice but was trying to save some time in building a script if someone already had one.
I also know this value is stored in the master database but was apprehensive about manipulating it.
Thanks...
John
February 18, 2008 at 3:38 pm
Run these commands to generate add & drop scripts for the devices you have already. The add scripts have the new drive letter.
[font="Courier New"]select 'exec sp_addumpdevice ''disk'', ''' + [name] + ''', ''' + REPLACE(UPPER([physical_name]), 'E:\', 'F:\') + ''''
from master.sys.backup_devices
select 'exec sp_dropdevice ''' + [name] + ''''
from master.sys.backup_devices
[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 19, 2008 at 4:40 am
Thanks. I'll check it out. Much appreciated.
February 27, 2008 at 12:02 pm
You can also use the GUI to modify the backup device properties.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
March 4, 2008 at 8:19 am
I am having similar issue. However, the destination of file is grayed out. Any idea why this might be?
March 6, 2008 at 3:06 pm
You can't edit it. Drop & add are your options. That field is there for when you setup a new one. You can't use it to edit an existing one.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
March 7, 2008 at 6:32 am
Thanks guys. I ended up deleting the old ones and creating new ones with the different path.
March 10, 2008 at 5:12 am
Hi Guys,
is it possible to backup to a network drive ???
March 26, 2008 at 12:10 pm
Yes it is possible. All my backup devices point to UNC paths on a Network SAN volume and work perfectly fine. In SQL 2000 there were issues but with 2005, the SQL agent handles this just fine.
March 27, 2008 at 7:49 am
But, if you search this site, UNC paths for backups are not recommended because a glitch in your network during the backup could corrupt the file. Having said that, at my previous employer, we used UNC paths and never had an issue. At my current job, everything is local, then swept to tape. Lots of different opinions on the topic. The main thing is to ensure you have good backups by restoring it to a dev/test environment from time to time and possibly running a CHECKDB against it. It helps me sleep better at night knowing I'm OK!
-- You can't be late until you show up.
March 27, 2008 at 8:45 am
how about this one
-- jobi dd 08/12/2003
-- verplaatsen backupdevices
use master
Declare @DevNameSuffix varchar(10)
Declare @OldPath varchar(500)
Declare @NewPath varchar(500)
select @DevNameSuffix = 'Log'
, @OldPath = 'T:\MSSQL\BACKUP\'
, @NewPath = 'T:\MSSQL\BACKUP\IncLogBackup\'
-- select @OldPath = 'U:\MSSQL$ALBE0DB78\BACKUP\'
-- , @NewPath = 'U:\MSSQL$ALBE0DB78\BACKUP\IncLogBackup\'
create table #TmpDevices(
device_name sysname ,
physical_name nvarchar(100),
description nvarchar(255),
status int ,
cntrltype smallint ,
size int )
declare @Tsql varchar(128)
declare @TsqlDrop nvarchar(500)
declare @TsqlAdd nvarchar(500)
set @Tsql = 'sp_helpdevice'
insert into #TmpDevices
exec (@Tsql)
declare csrDevices cursor for
select 'sp_dropdevice @logicalname = ''' + device_name + ''''
, 'sp_addumpdevice @devtype = ''disk'', @logicalname = ''' + device_name + ''', @physicalname = '''
+ replace(physical_name,@OldPath,@NewPath) + ''''
from #TmpDevices
where device_name like '%' + @DevNameSuffix
and cntrltype = 2
and physical_name like @OldPath + '%'
for read only
open csrDevices
FETCH NEXT FROM csrDevices
INTO @TsqlDrop, @TsqlAdd
WHILE @@FETCH_STATUS = 0
BEGIN
--T:\MSSQL\BACKUP --begin tran ReplLogDev --> The procedure 'sp_dropdevice' cannot be executed within a transaction.
exec ( @TsqlDrop )
exec ( @TsqlAdd )
--commit tran ReplLogDev
FETCH NEXT FROM csrDevices
INTO @TsqlDrop, @TsqlAdd
END
-- Cursor afsluiten
CLOSE csrDevices
DEALLOCATE csrDevices
--Opvragen nieuwe toestand
create table #TmpDevicesNew(
device_name sysname ,
physical_name nvarchar(100),
description nvarchar(255),
status int ,
cntrltype smallint ,
size int )
set @Tsql = 'sp_helpdevice'
insert into #TmpDevicesNew
exec (@Tsql)
-- drop table #TmpDevices
-- drop table #TmpDevicesNew
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 7, 2008 at 9:09 am
The forum may frown on it, but even Books online shows that using UNC is acceptable.
May 7, 2008 at 1:13 pm
Indeed, you can use UNC, as long as your service account/proxie account of your backup job has the rights to read/write at that location.
This also works on sql2000. (been using it for years)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 7, 2008 at 2:22 pm
I've used it for years as well, without issue. I do check the accuracy of my backups from time to time, just to be sure. I've heard all the stories about hiccups when going over the wire corrupting the files, but again, I've never had a problem.
-- You can't be late until you show up.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply