How to replace a test copy of sql 2005 db

• richard.hllnd

  Newbie

  Points: 5

  More actions

  June 4, 2008 at 2:00 am

  #188507

  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

• the__t

  Hall of Fame

  Points: 3260

  More actions

  June 4, 2008 at 8:13 am

  #824204

  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

• Peter Schofield

  Ten Centuries

  Points: 1364

  More actions

  July 4, 2008 at 7:36 am

  #838360

  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?

• MannySingh

  SSChampion

  Points: 12465

  More actions

  July 4, 2008 at 8:21 am

  #838367

  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