Moving a Device

The sp_movedevice procedure shown below lets you specify a new physical path for a logical device. This is useful if a user's drive letters have changed, or when you want to move the device.

Syntax

sp_move device device_name, new_path

Stored Procedure Code

/****************************************************************/
PRINT "
PRINT 'Creating Stored Procedure: dbo.sp_movedevice'
PRINT 'Use this prior to moving the device or DB will be suspect'
/***************************************************************/
go
sp_configure "allow", 1
RECONFIGURE WITH OVERRIDE
go
IF EXISTS (SELECT * FROM sysobjects WHERE
    name = 'sp_movedevice' and type = 'P')
    DROP PROCEDURE sp_movedevice
go
CREATE PROCEDURE sp_movedevice @@devname varchar(30), 
  @@newpath varchar (255)
AS
BEGIN
EXEC sp_configure "allow",1
RECONFIGURE WITH OVERRIDE
BEGIN TRAN
UPDATE sysdevices set phyname = @@newpath
WHERE name = @@devname
IF @@rowcount <> 1
BEGIN
PRINT "**********************ERROR**********************"
PRINT "Moved failed - more or less than one row affected"
PRINT "**********************ERROR**********************"
ROLLBACK TRAN
END
ELSE
BEGIN
PRINT "Device moved successfully"
PRINT "Change will take effect next time you start SQL Server"
COMMIT TRAN
END
EXEC sp_configure "allow",0
RECONFIGURE WITH OVERRIDE
END
go
sp_configure "allow",1
RECONFIGURE WITH OVERRIDE
go