Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_create_removable

  No additional text.


Syntax
create procedure sys.sp_create_removable
	@dbname		sysname = null,				-- name of db
	@syslogical	sysname = null,				-- logical name of system device
	@sysphysical	nvarchar (260) = null,	-- physical name of system device
	@syssize	int = null,					-- size of sys device in Meg.
	@loglogical	sysname = null,				-- logical name of log device
	@logphysical	nvarchar (260) = null,	-- physical name of log device
	@logsize	int = null,					-- size of log device in Meg.
	@datalogical1	sysname = null,			-- logical name of data device
	@dataphysical1	nvarchar (260) = null,	-- physical name of data device
	@datasize1	int = null,					-- size of data device in Meg.
	@datalogical2	sysname = null,			-- logical name of data device
	@dataphysical2	nvarchar (260) = null,	-- physical name of data device
	@datasize2	int = null,					-- size of data device in Meg.
	@datalogical3	sysname = null,			-- logical name of data device
	@dataphysical3	nvarchar (260) = null,	-- physical name of data device
	@datasize3	int = null,					-- size of data device in Meg.
	@datalogical4	sysname = null,			-- logical name of data device
	@dataphysical4	nvarchar (260) = null,	-- physical name of data device
	@datasize4	int = null,					-- size of data device in Meg.
	@datalogical5	sysname = null,			-- logical name of data device
	@dataphysical5	nvarchar (260) = null,	-- physical name of data device
	@datasize5	int = null,					-- size of data device in Meg.
	@datalogical6	sysname = null,			-- logical name of data device
	@dataphysical6	nvarchar (260) = null,	-- physical name of data device
	@datasize6	int = null,					-- size of data device in Meg.
	@datalogical7	sysname = null,			-- logical name of data device
	@dataphysical7	nvarchar (260) = null,	-- physical name of data device
	@datasize7	int = null,					-- size of data device in Meg.
	@datalogical8	sysname = null,			-- logical name of data device
	@dataphysical8	nvarchar (260) = null,	-- physical name of data device
	@datasize8	int = null,					-- size of data device in Meg.
	@datalogical9	sysname = null,			-- logical name of data device
	@dataphysical9	nvarchar (260) = null,	-- physical name of data device
	@datasize9	int = null,					-- size of data device in Meg.
	@datalogical10	sysname = null,			-- logical name of data device
	@dataphysical10	nvarchar (260) = null,	-- physical name of data device
	@datasize10	int = null,					-- size of data device in Meg.
	@datalogical11	sysname = null,			-- logical name of data device
	@dataphysical11	nvarchar (260) = null,	-- physical name of data device
	@datasize11	int = null,					-- size of data device in Meg.
	@datalogical12	sysname = null,			-- logical name of data device
	@dataphysical12	nvarchar (260) = null,	-- physical name of data device
	@datasize12	int = null,					-- size of data device in Meg.
	@datalogical13	sysname = null,			-- logical name of data device
	@dataphysical13	nvarchar (260) = null,	-- physical name of data device
	@datasize13	int = null,					-- size of data device in Meg.
	@datalogical14	sysname = null,			-- logical name of data device
	@dataphysical14	nvarchar (260) = null,	-- physical name of data device
	@datasize14	int = null,					-- size of data device in Meg.
	@datalogical15	sysname = null,			-- logical name of data device
	@dataphysical15	nvarchar (260) = null,	-- physical name of data device
	@datasize15	int = null,					-- size of data device in Meg.
	@datalogical16	sysname = null,			-- logical name of data device
	@dataphysical16 nvarchar (260) = null,	-- physical name of data device
	@datasize16	int = null					-- size of data device in Meg.
as
	declare @retcode int,
		@exec_str nvarchar (max),
		@numdevs int

	if (not (is_srvrolemember('sysadmin') = 1)) -- Make sure that it's the SA executing this.
	begin
		raiserror(15247,-1,-1)
		return(1)
	end

	if @dbname is null
		or @syslogical is null
		or @sysphysical is null
		or @syssize is null
		or @loglogical is null
		or @logphysical is null
		or @logsize is null
		or @datalogical1 is null
		or @dataphysical1 is null
		or @datasize1 is null
	begin
		raiserror (15261,-1,-1)
		return (1)
	end

	if exists (select * from master.dbo.sysdatabases where name = @dbname)
	begin
		raiserror(15032,-1,-1,@dbname)
		return(1)
	end

	-- Check to verify that valid sizes were supplied for required devices.
	if @syssize < 1 or @logsize < 1 or @datasize1 < 1
	begin
		raiserror (15262,-1,-1)
		return(1)
	end

	-- Check to see if a valid database name was supplied.
	EXEC @retcode = sys.sp_validname @dbname
	if @retcode <> 0
		return(1)

	-- valid syslogical?
	EXEC @retcode = sys.sp_validname @syslogical
	if @retcode <> 0
		return(1)

	-- valid loglogical?
	EXEC @retcode = sys.sp_validname @loglogical
	if @retcode <> 0
		return(1)

	-- valid datalogical1?
	EXEC @retcode = sys.sp_validname @datalogical1
	if @retcode <> 0
		return(1)

	-- Create the database's system device segment.
	select @exec_str = 'CREATE DATABASE '
			+ quotename( @dbname , '[')
			+ ' ON (NAME ='
			+ quotename( @syslogical , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@sysphysical ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@syssize)
			+ ') LOG ON (NAME='
			+ quotename( @loglogical , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@logphysical ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@logsize)
			+ ')'
	EXEC(@exec_str)

	if @@error <> 0
	begin
		raiserror(15264,-1,-1,'system or log')
		return(1)
	end

	-- Add a filegroup for data
	select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+ ' ADD FILEGROUP readonlyfilegroup'

	EXEC(@exec_str)

	if @@error <> 0
	begin
		raiserror(15264,-1,-1,'user filegroup')
		return(1)
	end

	select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical1 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical1 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize1)
			+ ') TO FILEGROUP readonlyfilegroup'
			EXEC(@exec_str)

	if @@error <> 0
	begin
		raiserror(15264,-1,-1,'user data')
		select @exec_str = 'DROP DATABASE '
				+ quotename( @dbname , '[')
		EXEC(@exec_str)
		return(1)
	end

	-- Make this the default filegroup
	select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+ ' MODIFY FILEGROUP readonlyfilegroup DEFAULT'

	EXEC(@exec_str)

	if @@error <> 0
	begin
		raiserror(15264,-1,-1,'default filegroup')
		return(1)
	end

	-- Check out optional data devices.
	if @datalogical2 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical2 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical2 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize2)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical2)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 2
	end
	else
		goto no_more_devs

	if @datalogical3 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical3 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical3 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize3)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical3)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 3
	end
	else
		goto no_more_devs

	if @datalogical4 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical4 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical4 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize4)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical4)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 4
	end
	else
		goto no_more_devs

	if @datalogical5 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical5 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical5 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize5)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical5)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 5
	end
	else
		goto no_more_devs

	if @datalogical6 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical6 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical6 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize6)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical6)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 6
	end
	else
		goto no_more_devs

	if @datalogical7 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical7 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical7 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize7)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical7)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 7
	end
	else
		goto no_more_devs

	if @datalogical8 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical8 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical8 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize8)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical8)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 8
	end
	else
		goto no_more_devs

	if @datalogical9 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical9 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical9 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize9)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical9)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 9
	end
	else
		goto no_more_devs

	if @datalogical10 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical10 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical10 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize10)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical10)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 10
	end
	else
		goto no_more_devs

	if @datalogical11 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical11 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical11 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize11)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical11)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 11
	end
	else
		goto no_more_devs

	if @datalogical12 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical12 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical12 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize12)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical12)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 12
	end
	else
		goto no_more_devs

	if @datalogical13 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical13 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical13 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize13)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical13)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 13
	end
	else
		goto no_more_devs

	if @datalogical14 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical14 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical14 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize14)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical14)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 14
	end
	else
		goto no_more_devs

	if @datalogical15 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical15 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical15 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize15)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical15)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 15
	end
	else
		goto no_more_devs

	if @datalogical16 is not null
	begin
		select @exec_str = 'ALTER DATABASE '
			+ quotename( @dbname , '[')
			+' ADD FILE (NAME ='
			+ quotename( @datalogical16 , '[')
			+ ',FILENAME ='
			+ ''''
			+ REPLACE(@dataphysical16 ,N'''',N'''''')
			+ ''''
			+ ',SIZE ='
			+ convert(varchar(28),@datasize16)
			+ ') TO FILEGROUP readonlyfilegroup'
		EXEC(@exec_str)
		if @retcode <> 0
		begin
			raiserror(15269,-1,-1,@datalogical16)
			select @exec_str = 'DROP DATABASE '
					+ quotename( @dbname , '[')
			EXEC(@exec_str)
			return(1)
		end
		select @numdevs = 16
	end

no_more_devs:
	return(0)	-- sp_create_removable

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash