Unanswered: database structure backup without data
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).
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.
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:\
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....
You can use the GUI tool like DTS, which you can generate a script and schedule it to run whenever you like.