December 30, 2009 at 11:40 am
Is there anyway to just backup the structure and not the data in SQL 2008? Is it possible within SSIS?
December 30, 2009 at 11:45 am
May be possible using any 3rd party tools.
You can use scripting all objects in a database to achieve this.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 30, 2009 at 1:04 pm
For me - the easiest method is using SQL Compare from redgate. You can download a fully functional trial version to test it out. Since I use this myself, I can guarantee that it pays for itself in no time.
To do this, create a brand new blank database. This is going to be your new database with schema only. Now, perform a comparison between your actual database and this new blank database. SQL Compare will generate a script that can be used to build the schema in the new blank database.
You can either perform the synchronization - or you can save the script and run it against another database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2009 at 3:09 pm
I would like to use SQL and not a third party tool. Is scripting objects possible in SSIS?
December 30, 2009 at 3:26 pm
ToddJames (12/30/2009)
I would like to use SQL and not a third party tool. Is scripting objects possible in SSIS?
Yes - scripting objects is possible in SSMS (not SSIS). You can right-click on any object in Object Explorer and script it. You can right-click on the database, select Generate Scripts and script all objects also.
However - SSMS is not smart enough to script all of the objects in the appropriate dependency order (if you select a single file). If you select a separate file for each object - then you have to run each object manually, and again - you will have lots of failures and have to re-run scripts after creating the other objects.
There are free versions of comparison tools available which will do the same thing - if the cost of the third-party solution is the problem. I still recommend using SQL Compare (or any comparison tool) for this - but, you do have the option of scripting everything yourself out of SSMS.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2009 at 3:29 pm
What about the new objects that get created?
December 30, 2009 at 3:37 pm
ToddJames (12/30/2009)
What about the new objects that get created?
Not sure I understand your question - but with a comparison tool, you run the comparison and new objects are identified which can then be synchronized. Additionally, you can track changes and apply the changes (synchronize) to your schema only version of the database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2009 at 10:23 pm
I want to backup the structure of the tables and not the data. How can I have the script be dynamic in the sense when new tables are created their structure will be backed up. Sorry if I was not clear in my previous post.
December 31, 2009 at 2:41 am
When I develop I usually have deployment document where I list the order of the scripts that I need to run. Alternatively I establish the order of the scripts, stick them all into 1 file and run the script to create the structure. I also have a second script where I reverse the order to drop all the objects.
I came across dynamic SQL (you might have to google for it) which simply removed everything from all the tables. You may run that and then take the backup of what you are left with. Not great, but it saves you from using 3rd party tools.
December 31, 2009 at 2:49 am
can you post an example?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply