Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: database structure backup without data

    Hello Everyone,

    I have a customer that needs us to develop a database application for him, but I have _no_ experience with MS SQL 2000. I do have experience with MySQL, and I need help (please!) telling the customer how to use his MS SQL 2000 to dump the structures of the tables (and databases) without dumping the data. (it is patient data so he can't let go of it).

    Thanks.

  2. #2
    Join Date
    Sep 2003
    Posts
    16
    What do you mean by "dump table structures" ? You want to backup the table structures without backing up the data?

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    yes - that is exactly correct.

    I need the database structure (database layout, table layout, column types and layout, etc) without the data.

    Thanks.

  4. #4
    Join Date
    Sep 2003
    Posts
    16
    The best way I can think of is to use truncate... truncate table <tablename> will empty the table. After truncating the tables, you can back up the database normally.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, a clarification since you are a newbie.

    When you backup or "dump" a database, you don't lose all the information in the databases. You are making a copy of the data and structure that can be restored on a different system or in case your database is damaged. So there is no harm in dumping the database.

    meeraarvind's suggestion will work for creating a backup with no data, but don't do it on your production database because truncate WILL delete all the data in your table(s).

    If you just want a copy of the database without any data, then script the entire database using Enterprise Manager. Then you can run this script to recreate a virgin database with no data. The downside of this is that many databases contain lookup tables, and these will be empty along with all the data tables.

    blindman

  6. #6
    Join Date
    Sep 2003
    Posts
    3

    THANKS!!!!

    Hey folks!

    Thank you so much for the help! You all are great!

  7. #7
    Join Date
    Jun 2003
    Posts
    31
    There is another way to do it, through the command prompt. You can create a CMD file (like below) to extract all the dtabase objects (table, view, stored-pro, function...) to flat files and use these files to recreate
    them at another server (you must re-created the database fisr, of course) :

    REM-- Assumed the SQL Server was installed on D:\
    D:
    cd D:\MSSQL\Upgrade

    REM Type the command all in ONE line
    scptxfr /s <server name> /d Northwind /I /F <directory where the output will be saved> /r /E /X /Y


    Then schedule a job to run the script above by daily/weekly/monthly....

    OR

    You can use the GUI tool like DTS, which you can generate a script and schedule it to run whenever you like.

Posting Permissions

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