Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I work on a database structure that has a lot of data in it. This database structure is used by approximately 40 different servers. I want to change some of the table structures, by adding some columns to tables, etc, but I do not have time to change 40 database structures.

How can I change all databases structure without losing any data with a script?

Can I backup a database structure without data and restore it to the other servers without them losing their data?

The servers are Microsoft SQL Server 2005.

share|improve this question

migrated from stackoverflow.com Feb 14 '12 at 13:30

This question came from our site for professional and enthusiast programmers.

1  
You can add columns to tables without dropping/recreating the table. See MSDN. Just write a script and distribute it? –  Blorgbeard Feb 14 '12 at 12:50
    
Do you normally use the GUI to generate changes, or your own ALTERs? Can you use 3rd party tools to package changes (eg Red Gate, Apex etc) –  gbn Feb 14 '12 at 13:53
    
ALTER TABLE is fine if the column is being added to the end of the column. I personally do not like the use of the ALTER as it does not modify the create date associated with that object which is sometimes problematic if one's management is too carefree about who is allowed to modify database objects. –  jl01 Feb 14 '12 at 15:19
    
@jl01, columns should alwys be added at the end. If you add them in the middle you have to recreate the table and move the data which is impractical in database terms once you reach a particular size. Code accessing the database should never use select * so the order of the columns in the table is irrelevant. –  HLGEM Sep 6 '12 at 20:44

2 Answers 2

You cannot backup the structure and restore that structure to another DB using SSMS; though there are third party tools that can help you, like Redgate's SQL compare which I have used previously for this.

One suggestion is to save your TSQL scripts, and then you can apply these scripts to the other DBs by using Powershell.

Another option is to use SQL CMS, but you will need at least one designated SQL 2008 instance.

share|improve this answer
    
I user a script for change the database but Redgate's SQL compare seems to be more easy way. And I can use it on the sql server 2005. Thanks a lot. –  Tuğba Feb 15 '12 at 15:10

The best way to use is a software that is designed to compare and synchronize the database structure. They will make sure that no data is lost during structure synchronization. ApexSQL has a good tool for this.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.