4

I have an c#.net windows based application that uses a database in Microsoft SQL Server 2008. During deployment for very first time to our client(s), we create a copy of our database and deploy it on client(s) remote server along with the UI application. The client database can be on version SQL Server 2005 and higher.

During times the UI application and associated database has gone lots of changes. Since this is a thick client application the client(s) database is not sync with our latest database and unfortunately no one ever made notes of all the changes done. So my challenges are as follows:

  1. How to find any missing columns on database table in Client's Database as compared to my Database? if any?

  2. How to find any missing Primary/Unique Constraints on database table in Client's Database as compared to my Database? if any?

  3. How to find any missing Indexes on database table that exist in Client's Database as compared to my Database? if any?

Please keep in mind the client(s) database size may ranges from 10-100GB, so i cannot plan to just drop all client tables and recreate it.

DevCod
  • 280
  • 2
  • 11
  • There are specific system tables and views that you can query and discover the schema actually in use in your client premise. It is up to you to make the comparison. (Or you could use a tool that updates the target for you and then start to take a diligent approach to this issue) – Steve Oct 08 '15 at 20:06
  • 1
    If you works with VS2005+ Premiun or Ultimate you do have a free native tool for schema and data comparation https://msdn.microsoft.com/en-us/library/aa833435(v=vs.100).aspx – jean Oct 08 '15 at 20:16
  • Going forward you should get your database under source control and keep better development notes!!! – Sean Lange Oct 08 '15 at 20:22

6 Answers6

2

You can use Data-tier applications. It's built-in feature of SQL Server, so you don't need to use any extra tools.

You can extract data-tier application from your database (in SSMS right-click -> Tasks -> Extract data-tier application) to a DACPAC file, copy the file to the client's server and use it to upgrade the DB there (or generate update script).

It also integrates nicely with SQL Server Data Tools.

Jakub Lortz
  • 14,616
  • 3
  • 25
  • 39
  • I liked your answer, it should really suits my needs well. I was not aware of this option, I will try it out and let you know here. Thanks. – DevCod Oct 08 '15 at 20:40
1

For this task, you need a software that compare SQL database. Just like there is a lot of software to compare text, there is a lot to compare database.

Personally, I use AdoptSQLDiff, but there is a bunch. RedGate has developed one also and I know others exists. Just type SQL Database compare in google to find them. You probably can have the job done with the trial period.

These softwares show you which tables was added, deleted or changed. It does the same for views, indexes, triggers, Stored Procedures, User Defined Functions, Constraints. More importantly, those tools generate script to push modifications into the target database. Very handy, but have a look at the script generated, it sometime messes it up by deleting data, but it can be fixed very easily.

There is also the option to compare data in a specific table if you need to.

Here is a screen shot of the interface of another so you know what it's look like. enter image description here

AXMIM
  • 2,424
  • 1
  • 20
  • 38
  • He probably already uses VS and it has built-in sql diff tool. – vittore Oct 08 '15 at 20:23
  • Comparing databases can get extremely complex, depending on how many bells and whistle you used. I tried doing this by hand long ago, and never could cover everything. Getting a third-party utility explicitly designed to do this work is completely worth the money; price things out, and check out online reviews/comparisons. (We use Redgate's SQLCompare). I'd think the most important thing in your situation is their ability to generate "diff" scripts that will modify the target database to account for the differences. – Philip Kelley Oct 08 '15 at 20:24
  • ...of course, no third-party tool can fully account for any subtle (or otherwise) **data** differences between your database versions. That, you will have to do "by hand". – Philip Kelley Oct 08 '15 at 20:25
0

With SQLServer Management Studio, you can try selecting a database and then Task->Generate Script, selecting appropriate options.

Do the same thing for the 2 db you want to compare. You will get two text files you can compare with a text file software comparer.

Comparison will highlight difference in the db structure.

Not the best way to do it, of course. But it can be a start. If the two dbs are not too different, you should be able to handle the differences

Better option, use some db comparer software. They are meant to compare db structure, constraint indexes and so on. Never used any of them, so cannot give any advice on that

Gian Paolo
  • 4,161
  • 4
  • 16
  • 34
0

If it is one time thing use any diff tool for DB, VS2010+ has a build in one, allows you to get difference for schema and data in two different files.

If you want to solve problem of your development process, you have wide range of options to implement versioning for data base.

  • If you are using EF - use Migrations, can't beat that.

  • If you are only on SQL Server and never looking at other RDBMS, check DAC ( Data-Tier applications, mentioned by Jakub)

  • Otherwise take a look at more generic solutions, among them I would reccomend you to take a look at DB.UP and if python code is good for you , check Alembic, it allow you to write your migrations using really nice python API.

  • if nothing works for you, create snapshot of current db schema and start doing differential scripts that you can use with self written tool or DB.UP

vittore
  • 17,449
  • 6
  • 44
  • 82
0

I am not sure if this can help, but who knows.

So is there any way to restore the server database on your local environment? If the answer is yes, you can try to join system views for each database and compare them?

I propose something like this(was a quick solution, so please sorry for formatting and other common stuff).

USE [master]
GO

SELECT
    LocalDataBaseTable.name AS TableName,
    LocalDataBaseTableColumns.name AS [Column],
    LocalDataBaseTypes.name AS DataType,
    LocalDataBaseTableColumns.max_length,
    LocalDataBaseTableColumns.[precision]
INTO #tmpLocalInfo
FROM LocalTable.sys.columns as LocalDataBaseTableColumns 
INNER JOIN LocalTable.sys.tables AS LocalDataBaseTable
    ON LocalDataBaseTableColumns.object_id = LocalDataBaseTable.object_id
INNER JOIN LocalTable.sys.types AS LocalDataBaseTypes
    ON LocalDataBaseTypes.user_type_id = LocalDataBaseTableColumns.user_type_id

SELECT 
    ServerDataBaseTable.name AS TableName,
    ServerDataBaseTableColumns.name AS [Column],
    ServerDataBaseTypes.name AS DataType,
    ServerDataBaseTableColumns.max_length,
    ServerDataBaseTableColumns.[precision]
INTO #tmpServerInfo
FROM ServerTable.sys.columns as ServerDataBaseTableColumns 
INNER JOIN ServerTable.sys.tables AS ServerDataBaseTable
    ON ServerDataBaseTableColumns.object_id = ServerDataBaseTable.object_id
INNER JOIN ServerTable.sys.types AS ServerDataBaseTypes
    ON ServerDataBaseTypes.user_type_id = ServerDataBaseTableColumns.user_type_id

SELECT
    #tmpServerInfo.* 
FROM #tmpLocalInfo 
RIGHT OUTER JOIN #tmpServerInfo 
    ON #tmpLocalInfo.TableName = #tmpServerInfo.TableName COLLATE DATABASE_DEFAULT
    AND #tmpLocalInfo.[Column] = #tmpServerInfo.[Column]  COLLATE DATABASE_DEFAULT
WHERE #tmpLocalInfo.[Column] IS NULL

DROP TABLE #tmpLocalInfo
DROP TABLE #tmpServerInfo

This will return all information about missed columns in your local database. The idea is to investigate 'sys' views and to find out if there any suitable solution for you.

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Mitrucho
  • 126
  • 9
0

You can use this simple script, which show you differences between tables, views, indexes etc.

Compalex is a free lightweight script to compare two database schemas. It supports MySQL, MS SQL Server and PostgreSQL.

or look at this question Compare two MySQL databases. This question about comparing two MySQL schemas, but some of listed tools supports MSSQL or have MSSQL version (for example http://www.liquibase.org/).

Another answer What is best tool to compare two SQL Server databases (schema and data)?

Community
  • 1
  • 1
DLevsha
  • 136
  • 3