SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 247 of 365 Next >

The technical professionals working in Database Management have multiple disciplines such as T-SQL coding, database design, security and platform issues, disaster recovery and others. You can look at the tasks asked of these professionals from two vantages: data tasks and management tasks. Data tasks involve things like programming and Business Intelligence, and management tasks involve things like backups and security.

No matter what the discipline, however, there is one need that most every database professional deals with at some time in their career: comparing two SQL Server databases to find changes or differences. There are many reasons that this task keeps coming up. In some cases the technical professional works in a development shop, either internally or commercially. The need here is pretty obvious — you have to be able to track the changes between product releases to upgrade one software version to the next. Making these change scripts and keeping the order and precedence straight can be very difficult.

Even if you don't develop in your shop, you may have to keep a copy of the schema lying around in case you need to write reports against the data in the commercial products you have that use SQL Server as a back end. When you get an upgrade to that product, it might break the reports you've written if the schema changes. Having a way to compare the schemas against each other gives you ability to track and fix your reports if that happens.

You also might be in a support or consulting role. If you've dealt with users much, you already know that the first question you ask when you hear about a problem is, "Was it working before? What changed?" and you also know the answer is "Yes. And nothing has changed. It just quit working." I've visited many sites where "nothing" has changed, and yet I find massive alterations to a commercially "sealed" database.

So it would seem with all these obvious needs to track database changes that it would be a common practice. Development professionals have a multitude of change management tools they use to track and control their code. It's even built-in to several design studio tools to integrate source-control into the development process. But it is not as common to see the schema of a database checked into or out of that source-control system. Many people, even technical professionals, believe that the database is a sealed product, something that can't or won't be altered by the end user. But it happens all the time.

Relational database systems aren't off the hook in this process either. Most don't offer any native way to track changes to the structure of the database, in a comprehensive way. Most of the time you don't "version" a database. You can enter a number into a table to do so, but the users are often free to edit that table at will, if they have DBA rights, so you really can't trust that number. You're still left with a need to compare two databases for differences.

There are commercial products that you can use to compare databases. The two I've used are Red-Gate's SQL Compare, and Idera's SQLConfig. Both work great, and the Red-Gate solution only costs a few hundred dollars (US) to buy. These are ideal because you just make a few selections and click a "Go" button. The scripts that each tool creates work almost every time, and they even keep the precedence and order correct automatically. Both will even implement the change with the click of a button. If you decide to purchase a solution, read on to find out the basic way they work and how you should use them.

If you decide to write your own comparison tool, you have several choices. In any case the basic process follows the same outline:

Read the database parameters and database objects
Script the Data Definition Language (DDL) statements such as CREATE, DROP and so forth for each object to a file or memory location
Repeat the process for the second database
Order the objects to line up with each other
Run a text comparison tool to find any differences
Create DDL scripts to implement the changes
Order the DDL scripts

It looks difficult, but up until the last two steps it's really quite easy to do. You don't have to buy anything, and in some cases you don't even have to code anything. Let's take a look a few methods you have to follow the outline.

First, you need to read the database objects. You can do that with Enterprise Manager in SQL Server 2000. You just right-click each object and then select All Tasks, then Generate SQL Script:

Figure 214

Of course, right-clicking each object and creating scripts on the screen is a bit too much work to do if you have more than a few objects. Fortunately, there's a much easier solution. In Enterprise Manager, just right-click the name of the database and click All Tasks and then Generate SQL Script:

Figure 215

You have multiple options that you can set in each tab. In the second tab you set formatting options to include things like descriptive headers and CREATE and DROP options:

Figure 216

In the last tab you can select options to create permissions and indexes:

Figure 217

SQL Server 2005 works in much the same way, but the same selection (All Tasks and then Generate SQL Script) brings up a full Wizard-driven interface with even more options:

Figure 218

Even though there is a wizard that will generate scripts for you, there's an easier way. In a previous series I explained how to use the Data Management Objects (DMO) engine and some simple scripts to call the same process that Enterprise Manager uses. You can use those scripts to create any kind of file you want and call any or all of your database objects. After all, why compare user-created tables to your commercial product? You can use DMO or the newer, more powerful Server Management Objects (SMO) in SQL Server 2005 to do the same thing. Whether you call the scripting engine manually or by using a little coding you can create your scripts.

So that takes care of steps one and two — getting the objects and saving them out to a script. For step three you just repeat the process on the other database, or change your scripts to point to the other database name.

For step four, if you use the scripting method you have more control over the process and can line up the objects automatically by calling the objects to be scripted in the same order. If you use the graphical tools, you'll need to ensure that the order of each object is the same, otherwise the next step won't work properly.

After you've created your scripts, you need to compare each line to see if there are any differences. I normally use a free text-comparison tool such as SED, but I'm an old UNIX guy and I like the command line. If you're more comfortable with a graphical tool, there are several out there, some for free and some not. There are a couple of potential issues here. For one, if you got your objects out of sequence, you'll get all sorts of false-positives. Lots of lines won't match if you're off by just one. The other problem is in the robustness of the comparison. You need to make sure that case doesn't matter, that it doesn't check for white space and that sort of thing.

When you get your differences file, which hopefully isn't very long, you can create your ALTER, DROP or CREATE statements to bring the second in line with the first. You'll have to be careful here, because changing a column might affect the data it contains or it may violate a constraint or relationship.

Informit Articles and Sample Chapters

I've reviewed some of Red-Gate's other tools here. In that review I talk a little more about the perils of database versioning.

Online Resources

You can also do a lot of this comparison right in Transact-SQL code. It's a bit of work, but you can learn more here.

< Back Page 247 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address