0

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.

4
  • 1
    You can add columns to tables without dropping/recreating the table. See MSDN. Just write a script and distribute it?
    – Blorgbeard
    Feb 14, 2012 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, 2012 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, 2012 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, 2012 at 20:44

2 Answers 2

1

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.

1
  • 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, 2012 at 15:10
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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