compare sql serve db against SSMS generated script file sql

robin banks

I have generated a .sql file of all db objects using SSMS-Tasks-Generate Scripts. How can I compare that against a live SQL Server database instance? 2008 R2 Express.

David Atkinson

This is not officially supported but should work.

Put your script (or scripts) in a separate folder. Create a new project in SQL Compare 8 Pro with your database to compare on one side and your folder of scripts on the other side (you'll have to pick Source Control and Scripts folder).

You are probably better off ignoring collations in the 'Options' for the project.

Hope this helps. Can I ask what you're trying to achieve?

David Atkinson
Red Gate Software

robin banks

Worked very nice. Thank you.

robin banks

What am I trying to achieve?

I generate full db script from SSMS. Save the .sql in Source Safe. Make some changes to dev db. Then I want to gen and diff my dev db script with the Source Safe copy to see what changes to deploy to production. SSMS rearranges the order of the objects in its generate script so I cannot just windiff it too easily (need to edit the file to rearrange).
Compare worked great.

robin banks

BTW: it did import date and time as [sys].[date], [sys].[time]

David Atkinson

Glad to hear that this worked for you.

We're actually adding VSS support to SQL Source Control. This will be in beta next week. Is this something you'd like to try as an alternative to your current process. the advantage is that this would be fully integrated into SSMS and you'd just need to click on 'Commit' once you've changed an object in SSMS, rather than having to script out each time. To see the differences between your Dev DB and source control you can simply visit the Commit tab and review the differences.

If you've not seen the tool, it's described here:
http://www.red-gate.com/products/SQL_Source_Control/

Let me know if you're interested and I'll put you on the notification list.

David

smiller

I generated a table script from SQL Server 2008 and compared to a snapshot. A field is defined as DATE in the script and DB, but shows as [sys].[date] in the script in the comparision report.

How can the conversion to [sys].[date] be prevented, or ignored when generating the comparision report?

Thanks.

ccurcillo

smiller wrote:

I generated a table script from SQL Server 2008 and compared to a snapshot. A field is defined as DATE in the script and DB, but shows as [sys].[date] in the script in the comparision report.

How can the conversion to [sys].[date] be prevented, or ignored when generating the comparision report?

Thanks.

...having same issue. any solution?

ccurcillo

ccurcillo wrote:

smiller wrote:

I generated a table script from SQL Server 2008 and compared to a snapshot. A field is defined as DATE in the script and DB, but shows as [sys].[date] in the script in the comparision report.

How can the conversion to [sys].[date] be prevented, or ignored when generating the comparision report?

Thanks.

On your data source screen, select "comparison settings". make sure that the SQL versions are in sync. I am guessing that yours is still set to 2005 (default), which does not know what a "date" data type is.

aspnerd

So I've scripted out in 2008 mode in SQL compare, to two folders, but even though the file I'm comparing does not have sys.date when it compares to another folder which only has date it says they are different...

So folder1 has [date] and folder2 has [date] but when it compares it says the file matches but when I look at the file it says folder1 is [date] and folder2 is [sys].[date]...

any ideas what is going on?