June 4, 2008 at 2:00 am
Hi. Just been dumped into a "take ownership of our db's " situation and am scared of making a mess. I need to update the test copy of a MS SQL 2005 db from the production copy and would like help re the easiest way to do this. The DBs are both on the same server.
I have tried the simple SQL bulk copy util on:http://projects.c3o.com/files/3/plugins/entry11.aspx
but it seems the tables are not the same in the 2 dbs (I understood they were) as the tool above gives me an "The given ColumnMapping does not match up with any column" I suppose I need to delete the test copy and make a new one. Any pointers re how to do this would be very much appreciated.
Thanks.
Richard
June 4, 2008 at 8:13 am
If your end-goal is to essentially have a copy of the Prod DB as a TestDB, you should use backup/restore instead of copy or bulk insert.
"Got no time for the jibba jabba!"
-B.A. Baracus
July 4, 2008 at 7:36 am
ahutch (6/4/2008)
If your end-goal is to essentially have a copy of the Prod DB as a TestDB, you should use backup/restore instead of copy or bulk insert.
Though good experience comes from doing the same thing in different ways - which was quickest? what were the advantages with each method?
July 4, 2008 at 8:21 am
Take a current backup of the Production Database.
Make sure you dont have any IMP Code changes(SP/Tables/Functions) in the Development/TEST Environment beore doing the RESTORE.
Backup the Development Database aswell.
Then copy the .bak File over to the PROD Server and run these Querries.
[Are your Development Database name and Production Database name same.]
KILL ALL CONNECTIONS TO THE DEVELOPMENT DATABASE
THEN RUN THESE STATEMENTS
RESTORE DATABASE [DATABASENAME]
WITH REPLACE
OR
RESTORE DATABASE [DATABASENAME]
WITH REPLACE,
MOVE 'LOGICAL DATA FILE NAME' TO 'PATH+FILENAME AND .MDF',
MOVE 'LOGICAL LOG FILE NAME' TO 'PATH+FILENAME AND .LDF'
Maninder
www.dbanation.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply