How to create a local copy of dbSNP

1.  dbSNP database organization brief overview:
dbSNP is a 'hub-and-spoke' design with dbSNP_main as the hub storing all the ‘lookup’ tables that are shared by each organism specific databases. Each spoke is an organism-specific database, named by organism_taxid(ex.: human_9606) that contains the latest data from submission, mapping, clustering, genotypes, haplotypes, validation etc.

2. Files needed to create a local copy of dbSNP:
To create a local copy of dbSNP, go to the following ftp directory:
ftp://ftp.ncbi.nih.gov/snp/database/
There are 4 subdirectories:
/organism_data: Data for each organism specific database.
/organism_schema:                  Schema DDL(Data Definition Language) for organism specific database.
/shared_data: Data in dbSNP_main that is shared by all organism database.
/shared_schema: Schema DDL for dbSNP_main.

3. Data File Format:
The entire data of dbSNP tables are dumped table-wise into files named as "tablename.bcp" under “shared_data” and “organism_data” subdirectories.  Fields of data within each file are tab delimited. (Note, this is an update to file on 11/22/99. The delimiter used to be '|'. ) ´

4. General Steps to create local dbSNP:
******************************************************************
First create dbSNP_main with schema files in /shared_schema and data in
/shared_data.  After dbSNP_main is created, create the organism
specific database using files in /organism_schema and /organism_data. 
For example, to create human_9606 database:

In ftp://ftp.ncbi.nih.gov/snp/database/shared_schema/:

Use dbSNP_main_table.sql.gz to create tables for dbSNP_main.
Use dbSNP_main_view.sql.gz to create views for dbSNP_main.
Use dbSNP_main_index_contraint.sql.gz to create indexes for dbSNP_main.

In ftp://ftp.ncbi.nih.gov/snp/database/shared_data/:
Use the <table>.bcp file to load into the tables.

Create database human_9606 with files in
ftp://ftp.ncbi.nih.gov/snp/database/organism_schema/human_9606,

human_9606_table.sql.gz
human_9606_view.sql.gz
human_9606_index_constraint.sql.gz 
human_9606_foreign_key.sql.gz      

Then load human_9606 table data with *.bcp files in
ftp://ftp.ncbi.nih.gov/snp/database/organism_data/human_9606.

Local dbSNP database should be re-created with each build, using the
latest schema file to ensure that all tables will be correctly loaded.

GUIDE
A sample Unix C shell script cmd.create_local_dbSNP has been provided
in ftp site:  ftp://ftp.ncbi.nih.gov/snp/database/loadscript.

cmd.create_local_dbSNP.txt shows how to use dbSNP_table.sql and *.bcp
to create a local sql database.  Users of other database platform can
use the same method with modifications of platform specific commands.

cmd.create_local_dbSNP first creates all tables without indexes. This
allows fast load of bcp data.  It then loads table data using a second
script cmd.bulkinsert.txt and compares lines in the data file with
table row counts as error checking.

Stepwise Procedure for Creating a Local Copy of dbSNP

http://www.ncbi.nlm.nih.gov/books/NBK21088/#ch5.ch5_s6

For fileformat description, see:

ftp://ftp.ncbi.nih.gov/snp/00readme.txt