Partager via

REMOVABLE DATABASES ON SQL SERVER

  • Article

Everyone that Works with SQL Server have needed to move a SQL database from one Instance to another in some point in their live, and probably has needed to deal with tapes, CDs or another media to copy the databases when it’s not possible to do it through the network. The files will most probably be placed on a SQL instance using a RESTORE operation or attaching the files (sp_attach_db). 

There are some specific circumstances on which you don’t want a database to be modified. You can try to do this by removing the write permissions on the tables but this can be overcome by a sysadmin login. However there is a way to move the data while been protected, this can be done by using removable databases, this databases are mostly read databases that normally have historical data that it’s not going to be change,

When you create a removable database on SQL Server three types of file are created:

  • A file containing la system tables
  • A file containing the data
  • A file containing the log

The log file and the file that contain the system tables need to be placed on a read/write unit.

Remember that a database could be expended on more than one storage unit, all this unit should be available when this database are online, so if a database needs 2 DVDs, the server would need to have to DVD Drives and have the to medias inserted,

Create a Removable Database

The process to create a removable database differs from the standard process, you should not use the CREATE DATABASE syntax, instead you should use a store procedure named sp_create_removable, and you will need to be part of the sysadmin server role to execute it.

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 ]

The @...16 parameters are used when the database use more tan one media (with a maximum of 16)

Example:

sp_create_removable 'BD_Removible',

'Rem_Sys', 'X:DATAremsys.mdf', 2,

'Rem_Log', ' X:DATA remlog.ldf', 2,

'Rem_Data', ' X:DATA remdata.ndf', 50

With this we created a database named BD_Removible (this is what you’ll see on management studio) y the files the comprise it are: system tables (2M), log file (2MB) and data file (50MB).

Once the database is created, you can use it as a regular database, create tables, reference, users and load the data.

When the database is ready to be copied to a media for its distribution, you need to use the store procedure sp_certify_removable. It works consist on update the statistics, check for possible problems, mask the data file-groups as read only and set the database as offline

Syntax:

sp_certify_removable [ @dbname = ] ' dbname ' [ , [ @autofix = ] ' auto ' ]

The System Administrator (SA) should be the owner of the Database and all the objects in it; this is because the SA will exist on all SQL installations and it will be used to the post administration and manipulation of rights, this is why it’s convenient to execute the store procedure with the autofix parameter for it to make the recommended changes. If you don’t use it any problem will stop the process and report the error.

Example:

sp_certify_removable BD_Removible, AUTO

If the store procedure runs without problems, the database will be put on an offline state.

Distributing the Removable database

The process is simple; you need to copy the files that compose the database to the media you are using to distribute it. To put online this Database you will need to attach it to the Servers (sp_attach_db or SQL Server Management Studio). You will need to have all the removable media where the files are, you only need to do this on time, now you just put it online or offline.

Remember that the log file and the system tables need to be copied to read/write unit, and you need to remove the read-only attribute.

The sp_certify_removable y sp_atach_db store procedures are mark on SQL Server 2008 as deprecated and will be discontinued on future releases, but thi could be useful for some situations on versions from SQL Server 7.0 to SQl Server 2008.

Comments

  • Anonymous
    January 01, 2003
    Very good, had never heard of that before. Will definitely prove useful