drop procedure sp_createdevice
go
create procedure sp_createdevice
(
@logical_data varchar(30),
@size_data int,
@physical varchar(255) = null
)
as
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	29 Jan 1997						*/
/*	Description:	This SP can be used to create the device file for a db. */
/********************************************************************************/
/*	Resources  :	http://www.umachandar.com/resources.htm         	*/
/********************************************************************************/
declare @retcode int,
	@mesg varchar(255),
	@exec_str varchar(255),
	@vdevno int,
	@size_in_blocks int,
	@sqlpath varchar(30),
	@drive varchar(3),
	@flag bit

/* Get next available device number for the data device. */
select @vdevno = null, @flag = case when @physical is null then 1 else 0 end
if @flag = 1
	select @physical = @logical_data
select @vdevno = (min(low)/0x01000000)+1
	from sysdevices d1
	where low/0x01000000 between 0 and 254
		and not exists
			(select * from sysdevices d2
			where d2.low/0x01000000 =
				(d1.low/0x01000000)+1)

if @vdevno is null or @@error <> 0
begin
	raiserror(15054,-1,-1)
	return(1)
end
select @drive = 'E:\'
select @sqlpath = @drive + 'sqldumps\'

-- create the data device file
select @physical = case when @flag = 1 then @sqlpath else null end + @physical + '.dat',
	@size_in_blocks = (@size_data*512.)
disk init
	name = @logical_data,
	physname = @physical,
	size = @size_in_blocks,
	vdevno = @vdevno

if @@error <> 0
	begin
		raiserror(15263,-1,-1,'data')
		return(1)
	end

/* Get next available device number for the log device. */
select @vdevno = null
select @vdevno = (min(low)/0x01000000)+1
	from sysdevices d1
	where low/0x01000000 between 0 and 254
		and not exists
			(select * from sysdevices d2
			where d2.low/0x01000000 =
				(d1.low/0x01000000)+1)

if @vdevno is null or @@error <> 0
begin
	raiserror(15054,-1,-1)
	exec sp_dropdevice @logical_data, delfile
	return(1)
end
exec sp_helpdevice @logical_data
return(0)
go