May 2, 2005 at 11:27 am
Hi, I need to compare two databases between different server, and I want to know if exist a instruction or a tool to do this, to compare primary keys, constraints, number of objects, to knok if they are equals or if exist differences between them.
Thanks for your help.
May 2, 2005 at 11:31 am
I assume that the need to compare arises out of your other post where the migration is failing to move constraints along with the data. If you restore your destination from a backup of your source database, the need to compare the two instances will be eliminated.
However, if you want database comparison tools that include data comparisons, I suggest that you look at tools by Apex or Red Gate or some other vendors that can be found via Google. However, these tools are typically not cheap. And SQL server has no built-in capabilities for this sort of thing.
May 2, 2005 at 12:05 pm
Red Gate has a tool that we use (SQL Compare) that is around $200.00 US per seat... Works like a charm gives you the option to review, apply either way, save, etc.....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 2, 2005 at 12:07 pm
It is possible to write some scripts to compare two databases in a relatively short period of time using the information_schema views and the system tables where necessary.
There are some free stored procedures about which do this as well.
http://www.sql-server-performance.com/vg_database_comparison_sp_code.asp
hth
dave
May 2, 2005 at 12:12 pm
I using two different ways to move my databases, one is Import or Export a database and the other to restore in a new location my databse using a backup.
As I reed, Importing or Exporting is not the best way to move databases, is better to restore from a database backup. Recently I used this way, restoring from a backup but when I check the new database I dont see my primary keys and my cosntraints, Is that possible?, why does it happen?, how can I be sure that It's not going to happen again?.
Thanks.
May 2, 2005 at 3:49 pm
As mentioned in the other thread, import/export may not copy your keys and constraints. But backup/restore will. If you can't see them, be absolutely sure that your restore worked and you're not still looking at the imported version. Verify that the backup is not an old backup that didn't include the keys/constraints. Also, be sure to refresh the view of the database in Enterprise Manager. EM is notorious for showing OLD information. Right click on the database and select refresh, then see if you can see your constraints. If you take a fresh backup of the database on server A, restore that on server B, the new database should be exactly as it was on server A. In fact, that's why users are complaining that their accounts don't work any longer. SQL assigns a "SID", which is a number associated with a loginid. When a login is created on server A, it gets a SID. When that same login is created on server B, it gets a different SID. The login in the new database on server B has server A's SID and is therefore "orphaned". You will need to execute sp_change_users_login to associate the database user with its loginid on server B. This will also happen if you do as David recommended in the other thread and detach/attach rather than backup/restore.
Steve
May 2, 2005 at 10:29 pm
er sql:
SQL Diff by ApexSQL compares all kind of objects. Its an excellent tool I have been using it for last 2 years.
I hope it will solve your problem.
Logon to http://www.apexsql.com for more details.
Ashfaq Chougle.
Database Administrator
IPS Group Inc.
May 3, 2005 at 3:16 am
to me comparing databases is usually a sign that you don't know what is in the database and therefore your change management system is faulty or non-existant. That being said you may just be auditing two databases as you've been told they're the same by someone and as you now own them you want to draw your own conclusions - if that is the case then by all means a Diff tool is the way to go.
Using diffs tools however to promote objects between databases however is ridiculous. I know alot of people will now be thinking I don't know what I'm talking about, but I have a system that bases itself on source control. I've never heard a valid argument against it and can point out many flaws in every other approach I've seen to database change management.
Here's a pdf on the subject: http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf
DB Ghost is the product that is the result of the process above...
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
May 3, 2005 at 10:49 am
May 3, 2005 at 4:31 pm
We use Red gates software as a sanity check between Production and Dev|Test|Staging to ensure that the 4 environments are back in sync after MAJOR installs.
This has helped (along with source code management) to make code promotion smoother because we are no longer worried about "What version" is in which environment....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 4, 2005 at 4:13 pm
Thanks, I got sql compare software to compare my databases.
Tanks everyone.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply