This topic has not yet been rated - Rate this topic

sp_create_removable (Transact-SQL)

SQL Server 2008

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.

Important
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.

Topic link icon Transact-SQL Syntax Conventions

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. This includes 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. This includes 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. Typically, 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. This includes 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

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:

Created	Modified to add a new file
Attached	Backed up
Detached	Restored

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.

Note
MicrosoftSQL Server 2005 Express Edition does not set data and log file permissions.

Examples

The following example creates the database inventory as a removable database.

EXEC sp_create_removable 'inventory', 
   'invsys',
   'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\invsys.mdf'
, 2, 
   'invlog',
   'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\invlog.ldf', 4,
   'invdata',
   'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\invdata.ndf', 
10

Reference

sp_certify_removable (Transact-SQL)

ALTER DATABASE (Transact-SQL)

sp_dbremove (Transact-SQL)

sp_detach_db (Transact-SQL)

sp_helpfile (Transact-SQL)

sp_helpfilegroup (Transact-SQL)

System Stored Procedures (Transact-SQL)

Concepts

Detaching and Attaching Databases

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD