August 11, 2008 at 9:37 am
Hi!
We are implementing a new system, but we have to backup and restore a databases that have information in the new server.
We need the databases structure and all the index, depentencies, (everything) but not the data, how can I do that?
August 11, 2008 at 9:46 am
Make an SSIS package with a transfer SQL server Objects task.
August 11, 2008 at 9:49 am
In SSMS right-click on the database and select All Tasks - Generate Scripts. First you should go to Tools -> Options -> Scripting and make sure all the pieces you need are included in the script task. Then once you generate the scripts you can apply them on the new server. Another option is to purchase a schema compare software like Red Gate SQLCompare and use it to transfer the schema.
Yeah - what Joel said, fewer steps.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 11, 2008 at 10:16 am
Hello,
Another method, assuming that you don’t have masses of data in your DB is to use SSMS to copy the DB (Right Click on the DB and select Tasks, Copy Database). After the DB is copied, truncate all of the tables in the new DB. (I should mention that this method is most useful if you want to retain some data e.g. in configuration or lookup tables).
Personally I prefer using Red Gate's SQL Compare. Just to expand on Jack Corbett's post, you can create an empty target DB and run a full compare between that and the DB you want to copy. You can then use the Synchronisation Wizard to populate the new DB with all of the objects, but without the data. You also have the (very) useful option to save the script generated, so you can re-run it as many times as you like e.g. on a UAT Server and then on a Production Server.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 11, 2008 at 11:07 am
Is Red Gate SQLCompare expensive?
August 11, 2008 at 11:07 am
Jack Corbett (8/11/2008)
In SSMS right-click on the database and select All Tasks - Generate Scripts. First you should go to Tools -> Options -> Scripting and make sure all the pieces you need are included in the script task.
One thing that folks should be aware of (I only found this out about 2 months ago), the SSMS Generate Scripts feature does not[/i] necessarily generate everything in the database.
Two things that I know of:
1) Security Info & DB Settings: Generally the "Script Database" feature will take care of these, however, it does not include the SQL Objects, so you will have to do both. And, if you intend to use them in a different server or environment, you will need to edit them some as Script Database contains some stuff (like disk & filenames) that may not be what you want somewhere else.
2) Service Broker Items: I think that it gets the Queues (they are SQL Objects), but nothing else from Service Broker as technically, they are not SQL Objects. So Message Types, Contracts, Services, etc. are not auto-scripted by anything in SQL Server 2005 that I know of. You can either script them one at a time through SSMS, or write your own SMO/SQL program to script them (which is what I had to do).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 11, 2008 at 12:11 pm
Teresita Castro (8/11/2008)
Is Red Gate SQLCompare expensive?
Not bad. If you are doing something like this often, or just want to be able to migrate schema changes it is worth the money.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 11, 2008 at 5:27 pm
If I decided to restore the databases in the other server, is there a way to erase all the data of the tables?
I mean an easy way
August 11, 2008 at 7:05 pm
It depends on the your design. If you have no FK's defined you can just do sp_msforeachtable 'truncate table ?' or sp_msforeachtable 'delete from ?', but if you have FK's you will need to do it in a specific order.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply