Options

compare sql serve db against SSMS generated script file sql

robin banksrobin banks Posts: 5
edited March 27, 2012 2:41PM in SQL Compare Previous Versions
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.
· Share on Twitter

Comments

  • Options
    David AtkinsonDavid Atkinson Posts: 1,439 Rose Gold 2
    edited November 24, 2010 11:38AM
    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
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • Options
    robin banksrobin banks Posts: 5
    Worked very nice. Thank you.
    · Share on Twitter
  • Options
    robin banksrobin banks Posts: 5
    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.
    · Share on Twitter
  • Options
    robin banksrobin banks Posts: 5
    BTW: it did import date and time as [sys].[date], [sys].[time]
    · Share on Twitter
  • Options
    David AtkinsonDavid Atkinson Posts: 1,439 Rose Gold 2
    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
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • Options
    smillersmiller Posts: 3
    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.
    · Share on Twitter
  • Options
    ccurcilloccurcillo Posts: 2
    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?
    · Share on Twitter
  • Options
    ccurcilloccurcillo Posts: 2
    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.
    · Share on Twitter
  • Options
    aspnerdaspnerd Posts: 9
    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?
    · Share on Twitter
Sign In or Register to comment.