sp_create_removable

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.

Syntax

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 ]

Arguments

[@dbname =] 'dbname'

Is the name of the database to create for use on removable media. dbname is sysname.

[@syslogical =] 'syslogical'

Is the logical name of the file that contains the system catalog tables. syslogical is sysname.

[@sysphysical =] 'sysphysical'

Is the physical name, including a fully qualified path, of the file that holds the system catalog tables. sysphysical is nvarchar(260).

[@syssize =] syssize

Is the size, in megabytes, of the file that holds the system catalog tables. syssize is int. The minimum syssize is 1.

[@loglogical =] 'loglogical'

Is the logical name of the file that contains the transaction log. loglogical is sysname.

[@logphysical =] 'logphysical'

Is the physical name, including a fully qualified path, of the file that contains the transaction log. logphysical is nvarchar(260).

[@logsize =] logsize

Is the size, in megabytes, of the file that contains the transaction log. logsize is int. The minimum logsize is 1.

[@datalogical1 =] 'datalogical'

Is the logical name of a file that contains the data tables. datalogical is sysname.

There must be from 1 through 16 data files. Usually, more than one data file is created when the database is expected to be large and must be distributed on multiple disks.

[@dataphysical1 =] 'dataphysical'

Is the physical name, including a fully qualified path, of a file that contains data tables. dataphysical is nvarchar(260).

[@datasize1 =] 'datasize'

Is the size, in megabytes, of a file that contains data tables. datasize is int. The minimum datasize is 1.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

If you want to make a copy of your database on removable media (such as a compact disc) and distribute the database to other users, use this stored procedure.

Permissions

Only members of the sysadmin fixed server role can execute sp_create_removable.

Examples

This example creates the database inventory as a removable database.

sp_create_removable 'inventory', 
   'invsys',
   'c:\Program Files\Microsoft SQLServer\MSSQL\Data\invsys.mdf', 2, 
   'invlog',
   'c:\Program Files\Microsoft SQLServer\MSSQL\Data\invlog.ldf', 4,
   'invdata',
   'c:\Program Files\Microsoft SQLServer\MSSQL\Data\invdata.ndf', 10

See Also

sp_attach_db

sp_attach_single_file_db

sp_certify_removable

sp_dboption

sp_dbremove

sp_detach_db

sp_helpfile

sp_helpfilegroup

System Stored Procedures