Hi experts
We are implementing a consolidated database which will evenutally contain the data of all the production databases around the world. It is basically a
replica of the production db with some extra fields.
The production databases are live (24/7 real time) as production is 24/7.
Coming sunday I have roughly half an hour to :
1. copy the data from the main production db (which is the largest db and is an existing live DB which will be the kick off for the consolidated db)
2. to effect some minor changes (adding some fields),
3. do a few large updates on these new fields. I have timed the updates in a development environment (equal to prod environment) and the updates alone
will take 15 minutes.
I am putting the Prod database in single user mode during this procedure. (to avoid any rogue users trying to continue even though they have been
told that the DB will be down for roughly 30 minutes from 3pm.)
The prod db and new consol db will reside on the same server (not my choice)
Plan A:
Backup Prod database.
Restore Prod Database to new consolildated db
create unique-identifiers for records in the new consol db ( 2 tables involved )
create 2 "temp tables" to hold id's (identiy seeds") versus unique identifiers
copy 2 tables across to prod db
add extra fields to prod db
update for unique identifiers and a few other extra fields
Plan B:
use sp_detach_db on the prod database.
use sp_detach_db from the new consolidated db (which at this stage is just an empty shell ie no tables nothing)
copy mdf and ldf files (from Prod) renaming them to the mdf and ldf files of the cons database
use sp_attach_db on the prod database
use sp_attach_db on the cons database
create unique-identifiers for records in the new consol db ( 2 tables involved )
create 2 "temp tables" to hold id's (identiy seeds") and unique identifiers
copy 2 tables across to prod db
add extra fields to prod db
update for unique identifiers and a few other extra fields
To clarify :
the other production db's have the required fields as they have been newly implemented. SO this is a once off procedure.
the other production db's have NO link with head-office (ie sql replication is out of the question).
I cannot use the Saturday nights backup as opposed to the current backup in Plan A as production is 24/7 and I need the most current data
when I copy across.
My question is which plan is better and faster.
BTW I have tried the TransferDatabase Task in DTS (by trying to make a copy of the development environment as a test) but for some reason this takes for ever (roughly 30 minutes) and at the end throws an error.
