SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Making SQL Server Tables Distributable

    I'm fairly new to SQL Server 2000 and I'm currently working on a project that requries a Microsoft SQL Server datasource, but I need to figure out a way to make certain tables portible. I need to create a CD-ROM with all of my scripts and data on it so I can take it to a client site and install everything from the CD, but I'm having problems getting my database tables into this kind of redistributable file. I have about 7 tables which I need in 3 different packages (so I can install one independantly of another), so this eliminates a straight backup/restore of the entire database. There are no relationships, but I do have (and need to keep) primary key and identity information. I've tried doing an export/import to an Access database, but this doesn't seem to keep my primary key and identity information. Is there any way to accomplish this task effectively? Since I will be restoring to an SQL Server, I don't care what kind of form the data is in on the CD-ROM, as long as I can easily import it into SQL Server.

    Thanks for any help you can give me,
    Goof
    Nathan Rutman
    A slightly offbeat creative.

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,289
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    In Enterprise manager, right click on the database you need to recreate, then select "all tasks" then "generate SQL script." Choose the options you need to have scripted, then click on the preview button. That should get you the script you would add to your CD-ROM (after editing where appropriate...)

    That script then can be run in query analyzer to get the tables into the database.

    Hope that helps...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dave,

    I stumbled upon that, but there isn't an option there to include the data from the tables - it just records the structure. I've asked some others (more knowledgable people than myself) and we collectively came the conclusion that I'd either have to code INSERT statements into the scripts that Enterprise Manager generates, or do 2 exports - one for those SQL scripts which contain the table structure, and one for the data that's in the database. There doesn't seem to be a good way to combine both into one file (which is retarded if you ask me - Microsoft should have thought of this kind of application).

    Goof
    Nathan Rutman
    A slightly offbeat creative.

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,289
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    You can use bulkinsert to insert the data into the database, and that can be scripted as well. This is what we use for transitioning from test to development to production. We generate the table build scripts like I told you, then export the appropriate data to text files and import those through bulk insert.

    It's not going to be a multi-user type of operation because there would be no point in having multiple versions of the SQL Server database. Defeats the purpose of it. They do provide the backup/restore but that's about it.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I just added INSERT statements to the end of the table creation scripts that Enterprise Manager generated.

    Thanks!
    Goof
    Nathan Rutman
    A slightly offbeat creative.

  6. #6
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Funnily enough, I just installed SQL Server 2000 this afternoon (having played with it a little in the past), and was wondering exactly the same thing...spooky...!
    Originally posted by Goof
    Yeah, I just added INSERT statements to the end of the table creation scripts that Enterprise Manager generated.

    Thanks!
    Goof
    Question is, how do you export the data in the tables as INSERT scripts suitable for execution once the table structure has been created?

    In practice, when it comes to it, I should be able to export the entire database directly via the export wizard, but I'd still like to know...!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  7. #7
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    M@rco,

    I only had about 10 records, so I just hand-coded the INSERT statements. Probably what you'll have to do is generate the SQL scripts for the table structure, and then export the data into a seperate file (Access, CSV, etc).

    Make sure you check on that database export. I would have thought exporting a table would have kept both structure and data (especially when exporting to Access, which supports equivilents of primary keys and identities and is even a Microsoft product!) but it didn't. Like I said, it sounds like an option that MS should have included in a second. I don't get it.

    Goof
    Nathan Rutman
    A slightly offbeat creative.

  8. #8
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Goof
    Make sure you check on that database export. I would have thought exporting a table would have kept both structure and data (especially when exporting to Access, which supports equivilents of primary keys and identities and is even a Microsoft product!) but it didn't. Like I said, it sounds like an option that MS should have included in a second. I don't get it.

    Goof
    Not sure if I am following you here. You ran an export from SQL Server to Access (assuming you used the Data Transformation Services) and didn't get the structure or data complete?
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  9. #9
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, I just want to export the SQL2000 DB (structure + data) to a single standard .sql file which would then let me recreate the entire database simply by executing it in enterprise manager.

    (Access <--> SQL via DTS is working fine)
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  10. #10
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh...

    Scripting a database in EM will not create the data like MYSQL and others. You could export it as a CSV file and just use your SQL and CSV files together in moving data. Or write a script to create INSERT commands for each row and append it to your SQL file.

    You may also want to hunt around for a stored procedure that does just that.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  11. #11
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's what I thought (and as Goof said earlier too). Shame they didn't think to include such a basic feature...!

    Anyway, doesn't matter too much - as long as I can connect with EM I can transfer stuff between the two DBs quite happily.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


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
  •