I simply want to use program to read database structure (schema) and save it into a file to be used to recreate DB structure

Posted on 2014-08-29
Last Modified: 2014-11-11
I am using c# and Microsoft SQL

Really my request is quite simple to be able to read database structure, ie tables and relations and stored procedures etc. and store it in a file.  So later I could execute the file and recreate the db in its entirety.  Not talking about data.
Question by:goodk
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    You can do all those from SSMS , you can right click the database you want to script , choose all the objects and generate the script
    LVL 68

    Assisted Solution

    Unfortunately it's not quite that simple.  There are LOTS of options that control the scripting.  You need to go thru all those options and verify that everything you need will be scripted.

    Honestly, if the data isn't large, it'd be easier and probably more accurate to restore the entire db, and then truncate/delete table data.

    Author Comment

    I am amazed that it is such a universal issue and no one has a generalized program to save the database structure in a file.  

    I am also wondering if there is a way to execute the SSMS script which saves the database via program?

    Author Comment

    Also, one more problem as I am using shared database it will not let me run SSMS scripts to save the file - so program is the only choice
    LVL 4

    Accepted Solution

    There is commercial 3rd party software to do what you're looking for but nothing free that I'm aware of, other than the manual scripting out of the database using SSMS as mentioned above.

    If you really want to do this from code, you need to take a look at SQL-SMO.  That is the object model that you can use from .NET to do all kinds of things within SQL Server.  In fact it's what SSMS uses.

    Author Closing Comment

    thank you

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now