Professional Web Applications Themes

MS SQL 2000 Data Migration w/ ColdFusion - Coldfusion Server Administration

We've recently started work on a website where the SQL datasources reside on a drive without much space. We therefore want to migrate the all the data sources from the C:\ drive to D:\ drive. I've been unsuccessful in uncovering much doentation. Is this as easy as (after backing up the data) moving the data to the new drive and reattaching to the new location of the data source? Are there other potential problems that I need to consider? Basically, I am looking for a description of the process of migrating MS SQL 2000 Data files with coldfusion....

  1. #1

    Default MS SQL 2000 Data Migration w/ ColdFusion

    We've recently started work on a website where the SQL datasources reside on a
    drive without much space. We therefore want to migrate the all the data
    sources from the C:\ drive to D:\ drive. I've been unsuccessful in uncovering
    much doentation. Is this as easy as (after backing up the data) moving the
    data to the new drive and reattaching to the new location of the data source?

    Are there other potential problems that I need to consider?

    Basically, I am looking for a description of the process of migrating MS SQL
    2000 Data files with coldfusion.

    ajsolimine Guest

  2. #2

    Default Re: MS SQL 2000 Data Migration w/ ColdFusion

    ajsolimine wrote:
    > We've recently started work on a website where the SQL datasources reside on a
    > drive without much space. We therefore want to migrate the all the data
    > sources from the C:\ drive to D:\ drive. I've been unsuccessful in uncovering
    > much doentation. Is this as easy as (after backing up the data) moving the
    > data to the new drive and reattaching to the new location of the data source?
    >
    > Are there other potential problems that I need to consider?
    >
    > Basically, I am looking for a description of the process of migrating MS SQL
    > 2000 Data files with coldfusion.
    >
    I suppose my first question would be why you're trying to do this with
    CF? Not that it's not doable, but migrating SQL Server databases (which
    is what I assume you're talking about) from one drive to another is
    really just a matter of either doing a backup and restore (at which
    point you can tell SQL Server where you want the files to reside) or
    detach the database, move the mdf file, and reattach the database. Does
    CF come into this picture because you have a lot of them to do and don't
    want to do them each individually?

    Also remember that pretty much anything that's possible via a wizard in
    SQL Server can be done with T-SQL commands as well (which is what
    enterprise manager does behind the scenes), so it's probably more a
    matter of determining what you need to do and how to do it in T-SQL,
    then if you want to get CF involved it would probably just be a mixture
    of cffile and cfquery that would get the job done.

    Matt

    --
    Matt Woodward
    [email]mpwoodward[/email]
    Team Macromedia - ColdFusion
    mpwoodward *TMM* Guest

  3. #3

    Default Re: MS SQL 2000 Data Migration w/ ColdFusion

    Hello ajsolimine -

    You asked:

    "Is this as easy as (after backing up the data) moving the data to the new
    drive and reattaching to the new location of the data source? "

    The short answer to your question would be yes it is that easy. You can
    detach graphically through Enterprise Manager or through Query yzer (using
    the sp_detach_db system stored procedure).

    After you move the database's physical .MDF and .LDF (and .NDF if you have
    more than one data files in your database) files to a different partition, you
    can re-attach the database graphically through Enterprise Manager or through
    Query yzer, (running the sp_attach_db system stored procedure).

    If other users are connected to a database when you try to detach it, you
    won't be able to do it. There's an easy work-around for this that I'll post
    later tonight or tommorow morning (I want to make sure I give you the correct
    syntax, and my daughter is waiting to play with me!)

    After you re-attach the database(s), you'll be done. You won't have to do
    anything ColdFusion wise. The DSN's (Data Source Names) won't need to be
    updated in any way, because the databases are still on the same server.

    Hope that helps, I'll post code on detaching databases even when they're in
    use later.

    Mike

    RelentlessMike Guest

  4. #4

    Default Re: MS SQL 2000 Data Migration w/ ColdFusion

    Here's the code you need to detach a database from within Query yzer:

    USE master
    ALTER DATABASE database_name
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    sp_detach_db 'database_name'

    The "SET SINGLE_USER WITH ROLLBACK IMMEDIATE" line will kill all connections
    to the database which you are trying to detach. This is neccessary in order to
    detach the database. All transactions currently in progress will be rolled
    back gracefully - The database won't be left in an inconsistent state. Also,
    when you re-attach the database, don't worry - It will be correctly reattached
    in multi-user mode, it won't still be in single-user mode.

    It is also possible to detach a database from within Enterprise Manager when
    other users are connected to it, but if the database is in constant use, you
    won't be able to point and click fast enough to kill the connections and then
    detach the database.

    Mike



    RelentlessMike Guest

Similar Threads

  1. Coldfusion MX and Microsoft SQL 2000
    By hanlk in forum Coldfusion Database Access
    Replies: 1
    Last Post: June 12th, 02:14 PM
  2. Coldfusion MX migration from windows to lunix
    By xinfg2133 in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: March 16th, 08:13 PM
  3. MS SQL Server 2000 to Coldfusion MX
    By crayongal in forum Coldfusion Server Administration
    Replies: 6
    Last Post: March 10th, 02:22 PM
  4. Replies: 0
    Last Post: November 14th, 10:39 PM
  5. Replies: 1
    Last Post: July 28th, 02:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139