Creates a removable media database. Creates three or more files (one for the system catalog tables, one for the transaction log, and one or more for the data tables) and places the database on those files.
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE instead.
sp_create_removable [ @dbname = ] 'dbname', [ @syslogical= ] 'syslogical', [ @sysphysical = ] 'sysphysical', [ @syssize = ] syssize, [ @loglogical = ] 'loglogical', [ @logphysical = ] 'logphysical', [ @logsize = ] logsize, [ @datalogical1 = ] 'datalogical1', [ @dataphysical1 = ] 'dataphysical1', [ @datasize1 = ] datasize1 , [ @datalogical16 = ] 'datalogical16', [ @dataphysical16 = ] 'dataphysical16', [ @datasize16 = ] datasize16 ]
Requires CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.
To maintain control over disk use on an instance of SQL Server, permission to create databases is typically limited to a few login accounts.
Permissions on Data and Log Files
In SQL Server 2005, certain permissions are set on the data and log files of each database. The following permissions are set whenever the following operations are applied to a database:
Modified to add a new file
The permissions prevent the files from being accidentally tampered with if they reside in a directory that has open permissions. For more information, see Securing Data and Log Files.
The following example creates the database inventory as a removable database.
EXEC sp_create_removable 'inventory', 'invsys', 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\invsys.mdf' , 2, 'invlog', 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\invlog.ldf', 4, 'invdata', 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\invdata.ndf', 10