Post reply

How can I copy a database for testing purposes (DB1 --> DB1_test)?

  • December 7, 2005 at 5:41 am

    #111841

    Hallo -- I'm barely beginning to get to grips with SQL Server 2000 & need advice!

    What I need to do is copy the structure of an existing database so that I can run seperate production & development (and test, for that matter) databases. How would I do this if the original, existing database was called MY_DB, and the structural copies I wanted to make were intended to be called MY_DB_dev and MY_DB_test?

    Thanks for any & all help,

       doug.

  • December 7, 2005 at 7:08 am

    #608158

    There're several ways to do this but the easiest and quickest one would be via Enterprise Manager - create 2 new databases called MY_DB_dev & MY_DB_Test - Use the "Import Data" wizard and import your database objects (or only the tables if you want) into your 2 new databases - uncheck the box that says "copy data" so that you have the schema but not the data...uncheck the box that says "copy all objects" if you want to pick only specific objects to be copied...







    **ASCII stupid question, get a stupid ANSI !!!**

  • December 7, 2005 at 7:13 am

    #608160

    There are a couple of ways to do this:

    1.  Use the detach and attach method.

    -  This involves copying the database files from one server to another and then attaching them as a new database to that server.

    -  Involves an outage of your production server since the files can't be copied while the database is running.

    2.  Use the copy database wizard.

    -  Fairly straight forward

    3.  Restore a backup of the Production Database.

    -  This is the method I use. 

    -  Very straight forward and requires no outage to your production system.

     

  • December 7, 2005 at 7:48 am

    #608173

    sushila -- thanks very much for that! It did the job perfectly!

    Journeyman -- thanks, too -- those are pointers into things I'm *really* going to need to look into.

    Thanks again,

       doug.

  • December 7, 2005 at 8:02 am

    #608176

    Great!







    **ASCII stupid question, get a stupid ANSI !!!**

  • December 8, 2005 at 1:38 pm

    #608528

    Another option might be to use Generate SQL Script in Enterprise Manager.  Select all the desired objects and look at the formatting options to make sure you get everything (indexes, keys, extended properties, etc).

    You can save the script in case you want to wipe out and recreate your test databases.

    There can be problems with the script if objects have been renamed.  The script for views and procedures contains the text used to create the object, but if the object was renamed the script might still have the original names.  (That's why you get a warning about renaming things).  This is not an issue for the backup/restore method.

    I like having the entire database script as a reference.  You may want to do a global search-and-replace for something like changing all TEXT fields to VARCHAR(MAX).

  • December 9, 2005 at 8:14 am

    #608677

    "I like having the entire database script as a reference."....couldn't agree more!

    I have it scripted and stored in source safe...







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply