How to get Difference between two databases?

 

As a DBA one of the task one would face at some point of time is to get the differenced between two databases. Generally we may resort to some third party tools such as SQL Diff, SSDT Schema compare etc.  Being a DBA it should not be that hard to get the difference between two databases. We could use the SQL Server's Object Catalog views and get all the differences we want.

 

Here are some general differences we want to compare

1. Tables present in Database 1 and Not present in Database 2
2. Tables not present in Database 1 and Present in Database 2
3. Tables present in Both the database But have column level changes
• Data type Changes
• Newly added columns
• Columns which were dropped

You can use the following set to queries to get the differences.

1. Tables present in Database 1 and Not present in Database 2

                    SELECT  t.name as "TableName FROM DB1.sys.tables t

            EXCEPT

            SELECT  t.name  FROM DB2.sys.tables t

 
2. Tables not present in Database 1 and Present in Database 2


                    SELECT  t.name as "TableName FROM DB2.sys.tables t

            EXCEPT

            SELECT  t.name  FROM DB1.sys.tables t

3. Column Changes(Data Type Changes)



                   SELECT  T.[name] AS [table_name], AC.[name] AS [column_name], 
              TY.[name] AS system_data_type,Ac.max_Length, Ac.precision,Ac.scale,
              T1.[name] AS [table_name_Old], AC1.[name] AS [column_name_old], 
              TY1.[name] AS system_data_type_Old
           FROM    DB1.sys.[tables] AS T 
           INNER JOIN DB1.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
           INNER JOIN DB1.sys.[types] TY ON AC.[user_type_id] = TY.[user_type_id]
           INNER JOIN DB2.sys.[tables] AS T1  on T.name=T1.name
           INNER JOIN DB2.sys.[all_columns] AC1 ON T1.[object_id] =
           AC1.[object_id] AND AC.name=AC1.name INNER JOIN DB2.sys.[types] TY1 ON
           AC1.[user_type_id] =TY1.[user_type_id] AND TY.name<>TY1.name
           ORDER BY t.name

4. Column Changes (Newly added Columns):
           SELECT Table_Name, Column_name,  type_name(b.user_type_id), b.max_length,
        B.precision,B.scale
        FROM
        (
            SELECT  T.[name] AS [table_name], AC.[name] AS [column_name] 
            FROM    DB1.sys.[tables] AS T 
            INNER JOIN DB2.MASDirect.sys.[tables] tx ON t.name=tx.name
            INNER JOIN [MASNEW].sys.[all_columns] AC ON T.[object_id] =
              AC.[object_id] 
            INNER JOIN [MASNEW].sys.[types] TY ON AC.[system_type_id] =
             TY.[system_type_id]
            EXCEPT
             SELECT 
             T1.[name] AS [table_name], AC1.[name] AS [column_name]   
             FROM   DB2.sys.[tables] AS T1  
             INNER JOIN DB2.sys.[all_columns] AC1 ON T1.[object_id] =
             AC1.[object_id]
            INNER JOIN DB2.sys.[types] TY1 ON AC1.[system_type_id] =
            TY1.[system_type_id]
            ) as A
            INNER JOIN DB1.sys.all_columns AS b ON a.table_name=

            OBJECT_NAME(object_id,db_id('DB1')) AND a.column_name=b.name
          ORDER BY table_name






5. Column Changes(Columns which were dropped)
         SELECT 
          T1.[name] AS [table_name], AC1.[name] AS [column_name]   
          FROM    DB2.sys.[tables] AS T1  
          INNER JOIN DB1.sys.[tables] tx on t1.name=tx.name
          INNER JOIN DB2.sys.[all_columns] AC1 ON T1.[object_id] =
          AC1.[object_id]
         INNER JOIN DB2.sys.[types] TY1 ON AC1.[system_type_id] =
         TY1.[system_type_id]
       EXCEPT
        SELECT 
          T.[name] AS [table_name], AC.[name] AS [column_name] 
         FROM DB1.sys.[tables] AS T  
        INNER JOIN DB2.sys.[tables] tx on t.name=tx.name
        INNER JOIN DB1.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
       INNER JOIN DB1. sys.[types] TY ON AC.[system_type_id] =
        TY.[system_type_id]


These queries may come handy. Hope it is useful to someone.

Taking backup of all the SQL server databases using a single TSQL Command

Is it possible to take the backup of all the SQL server databases in one single command?

Yes, It is possible to take the backup of SQL server databases in one single command. But it is an undocumented command so needless to say it is "AS-IS".
The command I used was sp_msforeachdb. The command given below takes the backup of all the databases including the system databases.

sp_msforeachdb 'backup database ? to disk="D:\\BACKUPLocation\?_dev.bak"'

You may see error in the output message about TEMPDB but that is fine. This cannot be tweaked further to take only user databases.     

Please leave a command if you like this tip.

Error: "Incorrect syntax near ')'." When Passing function to a Stored procedure

Let us say I have a requirement to pass the current date time to a stored procedure. 
For example let us consider the following SP
 
CREATE PROCEDURE [dbo].[DisplayDate](@Curr_Date DATETIME)

AS SELECT @Curr_Date
 
When I try to execute it with the parameter as getdate(). I would receive the following error

exec [DisplayDate] getdate()

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

 
Theorically it should have worked. There is a Limitation on the way we can use it with a stored procedures.

According to the Books online article on
Parameters (Database Engine)
http://technet.microsoft.com/en-us/library/ms190248(v=SQL.105).aspx

"When a stored procedure or function is executed, input parameters can either have their value set to a constant or use the value of a variable. Output parameters and return codes must return their values into a variable. Parameters and return codes can exchange data values with either Transact-SQL variables or application variables."

So it is clear that we cannot use any functions directly with an stored procedure.
So we have to look at other alternatives.

For example
The above stored procedure can be rewritten as
CREATE PROCEDURE [dbo].[DisplayDate]

AS SELECT Getdate()

So we need to consider the fact that we cannot pass the function directly to the stored procedure instead we have to either use it in variable or use the function directly.

 

How to get a SSL Configured on SQL server.

Getting a valid Certificate:

---

The first steps involves getting a valid certificate from the certificate autority. Since I do not have a Certificate Authority (CA) of my own. I will make use of a utility called MakeCert.exe which is part of .net Framework SDK. You can download it from the following link .NET Framework 2.0 Software Development Kit (SDK) (x86).

Just any certificate cannot be used to enable SSL on SQL server. There are certain requirements a certificate should meet. These are described in the following knowledge base article Encrypting Connections to SQL Server.

Certificate Requirements

---

For SQL Server to load a SSL certificate, the certificate must meet the following conditions:

• The certificate must be in either the local computer certificate store or the current user certificate store.
• The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
• The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
• The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
• The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
• SQL Server 2008 R2 and the SQL Server 2008 R2 Native Client support wildcard certificates. Other clients might not support wildcard certificates. For more information, see the client documentation and KB258858.

So inorder generate a proper certficate on MakeCert utility I used the following command

MakeCert "c:\temp\MyCertnew.cer" -pe -n "CN=Hostname.Contoso.com" -ss my -sr LocalMachine -a sha1 -sky exchange -eku 1.3.6.1.5.5.7.3.1 -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 -iR LocalMachine 

Once the command is completed you can check the Certificate store in the local computer for the certificate. It should look similar to the ones shown below.

 Now let us map each of the command to see if the certificate fulfils our requirement for SQL server.

-pe-- Create a Private Exportable Key.
-n "CN=Hostname.Contoso.com" -- Set the subject propert with the host FQDN name.
-ss -- Load the certificate in the local store.
-sr-Load the certificate in the local computer store.
-a Signature algorithm used.
-sky exchange-- Set Key spec option on AT_Exchange
-eku-- Enhanced Key used for setting certificate for server authentication.
-sp-- Cryptographic API provider
-sy-- CryptoAPI providers
-ir-- issue certificate store.

So we have got pretty much what is required for configuring SSL on the SQL Server.

Now the questions is how do we confirm that the certificate I have from the CA is valid
Let us verify the certificate properties.

The Hostname.contoso.com is a valid FQDN name
The validity of the certificate
and the message " You have a private key that corresponds to this certificate"

The Enhanced key usage property should show the purpose as Server authentication
the subject should also show the valid FQDN name.


Once we have validated it we can use this certificate to configure SQL server using SQL server configuration manager.
Right click on the SQL server 2005 Network configuration
Right click on Protocols for MSSQLSERVER and then select the certificate tab
Under the drop down you can see the certificate.

Also under the flags tab select " Force protocol encryption"

Now go ahead and restart the SQL server. Once we have restarted successfully we can look up the SQL server errorlog to confirm if the certificate is loaded correctly

We should see a message similar to

2013-01-25 13:14:27.150 Server The certificate was successfully loaded for encryption.

Now let us connect to the SQL server and verify if our connections are encrypted.

We could use the following DMV to check the status of the connections

 
please feel free to ask your question using the comment section.