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  :    https://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

This page was last updated on May 01, 2006 04:28 PM.