SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Question Importing EVERYTHING from SQL Server?

    Hey everyone.

    Here's the problem. My dad decided to buy an ASP website for his company, and the data is stored on SQLServer. Knowing I'm a web developer, he came to me (without knowing the first thing about server-side development, or the differences between PHP + ASP and MySQL + SQL Server), and asked me to migrate the whole site to his ASP-enabled server, including importing all of the data from SQLServer.

    The catch?

    Never working with SQLServer before, I'm not sure where to start. I've tried importing everything into Microsoft Access (groan) via an ODBC connection I set up, but that doesn't import any procedures, which the ASP site depends on.

    So, what's the best method of importing everything?

    I have the existing database details, however I don't have information for any DBMS connected to it (like MySQL has with PHPMyAdmin).

    Any Suggestions?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ok, I've downloaded Microsoft SQL Server Management Studio Express. I've managed to export the whole database (but without the INSERT statements unfortunately) to an SQL file.

    After attempting to import that data to the new SQL Server (using "myLittleAdmin", 1&1's SQL Sever administration, where it's hosted) I'm getting an error:

    Error -2147217900 : Invalid column name 'area_name'.

    The database is full of names like that (GA_Latest, Knitting_Needles etc).

    Anyone know why that column name would throw an error?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Problem solved, though I don't know why. Just inserted tables separately, then procedures etc.

    Now for alot of table-data exporting.

    fun! </sarchasm>
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i think the way to export something as a bunch of INSERT statements is to use the "generate script" command on the table. but i'm just drawing from much-disused memory here so i could be wrong.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Addict
    Join Date
    Jan 2007
    Posts
    344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using books online, look up the following two stored procedures:

    sp_detach_db
    sp_attach_db

    Perform the first on the original server, copy the file, perform the second on the new server. Finished.

  6. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,629
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    ^^^those don't work so hot if you don't have RDP access.

    The cool kids are using the Sql Server Hosting Toolkit to generaty mysql-like dumps. Personally I tend to schlep around backup files rather than dismounting. We like to keep our apps online and have the security organized in a way where this is not a problem.

    PS: Also, download the eval edition of Sql 2005. Then just install the client components. Instamagic full-blown client program.


Tags for this Thread

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
  •