tablediff.exe – Compare and Synchronize your SQL Server tables

Need to check if your tables in 2 different servers are out-of-sync? SQL Server comes with a command line tool that does the job. It’s called tablediff.exe (read up BOL entry for this nifty tool).

By default, you can find this in the COM directory of your SQL Server install folder.

In SQL Server 2005 by default it will be in:
C:\Program Files\Microsoft SQL Server\90

In SQL Server 2008 / SQL Server 2008 R2 by default it will be in:
C:\Program Files\Microsoft SQL Server\100

These are the switches you can specify for the tool:

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Administrator>cd C:\Program Files\Microsoft SQL Server\100\COM

C:\Program Files\Microsoft SQL Server\100\COM>tablediff.exe /?
Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:
/?

 Replication Diff Tool Command Line Options

        usage: tablediff

          -- Source Options --
 -sourceserver          Source Host
 -sourcedatabase        Source Database
 -sourceschema          Source Schema Name
 -sourcetable           Source Table or View
 -sourceuser            Source Login
 -sourcepassword        Source Password
 -sourcelocked          Lock the source table/view durring tablediff

          -- Destination Options --
 -destinationserver     Destination Host
 -destinationdatabase   Destination Database
 -destinationschema     Destination Schema Name
 -destinationtable      Destination Table or View
 -destinationuser       Destination Login
 -destinationpassword   Destination Password
 -destinationlocked     Lock the destination table/view durring tablediff

          -- Misc Options --
 -t             Timeout
 -c             Column Level Diff
 -f             Generate Fix SQL (You may also specify a file name and path)
 -q             Quick Row Count
 -et            Specify a table to put the errors into
 -dt            Drop the error table if it exists
 -o             Output file
 -b             Number of bytes to read for blob data types
 -strict        Strict compare of source and destination schema
 -rc            Number of retries
 -ri            Retry interval

Here is an example usage. Note this has to be all in one line at the Command Prompt (I just separated them out into different lines for clarity):
Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.0/10 (7 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)

How to script out your database objects (Database Publishing Wizard)

I work with MySQL, and MySQL has a mysqldump client that scripts out database schemas + records in the tables.

There is no “native” tool in SQL Server 2000/2005 that does this (as far as I know). Luckily there’s a SQL Server add-in that allows scripting of both schema + records.

You can download the Database Publishing Wizard from CodePlex:
http://www.codeplex.com/sqlhost/wiki/view.aspx?title=database%20publishing%20wizard

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SysInternals Tools

Sysinternals Suite :

http://technet.microsoft.com/en-ca/sysinternals/0e18b180-9b7a-4c49-8120-c47c5a693683.aspx

Includes:

BGInfo – displays configuration, such as its name, IP address, or operating system version as a desktop wallpaper

ZoomIt – screen zoom and annotation tool for technical presentations

ProcessExplorer – displays which program has a particular file or directory open

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`