locked
Compare database with old backup? RRS feed

  • Question

  • Hi

    i want compare sql databse with old backup via T-SQL?


    • Edited by ASP.NET, DEV Sunday, June 24, 2012 6:02 PM
    • Moved by Janet Yeilding Monday, June 25, 2012 4:04 AM (From:SQL Server Data Tools)
    Sunday, June 24, 2012 6:02 PM

Answers

  • Do you want to compare the data or structure as well? For structure I have been using SQLCompare tool from www.red-gate.com for years.

    For data, look at this one

    IF (SELECT COUNT(*) FROM Foo)
       <> (SELECT COUNT(*) FROM Bar)
    PRINT 'No'
    ELSE IF (SELECT COUNT(*)
               FROM Foo, Bar
              WHERE Foo.c1 = Bar.c1
                AND Foo.c2 = Bar.c2
                 ...
                AND Foo.cn = Bar.cn)
             = (SELECT COUNT(*) FROM Bar)
          AND (SELECT COUNT(*) FROM Foo)
             = (SELECT COUNT(*) FROM Bar)
         THEN 'Yes'
    END;


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Maggie Luo Sunday, July 8, 2012 4:29 PM
    Monday, June 25, 2012 7:12 AM
    Answerer
  • For data, look at this one

    IF (SELECT COUNT(*) FROM Foo)
       <> (SELECT COUNT(*) FROM Bar)
    PRINT 'No'
    ELSE IF (SELECT COUNT(*)
               FROM Foo, Bar
              WHERE Foo.c1 = Bar.c1
                AND Foo.c2 = Bar.c2
                 ...
                AND Foo.cn = Bar.cn)
             = (SELECT COUNT(*) FROM Bar)
          AND (SELECT COUNT(*) FROM Foo)
             = (SELECT COUNT(*) FROM Bar)
         THEN 'Yes'
    END;

    Nice and simple query, Thanks for sharing.

    I changed this query a bit to use INTERSECT for easy use

    IF (SELECT COUNT(*) FROM foo) <> (SELECT COUNT(*) FROM bar)
    	PRINT 'No'
    ELSE IF (SELECT COUNT(*)  FROM (SELECT * from foo INTERSECT SELECT * FROM bar)Tab1)
             = (SELECT COUNT(*) FROM Bar)
          AND (SELECT COUNT(*) FROM Foo)
             = (SELECT COUNT(*) FROM Bar)
         print 'Yes'
    ELSE
    print 'No'

    Regards
    Satheesh




    Monday, June 25, 2012 7:27 AM
    Answerer

All replies

  • Hi,

    To compare first you may need to restore the database and then use T-SQL to compare the two databases.

    For comparison you may need some tools like RedGate Db Comparison tools or similar products.

    Here is a simple TSQL Script that will help you in comparing  objects in Database

    http://gallery.technet.microsoft.com/Compare-two-databases-for-b44e7afa

    you can use this procedure as this

     EXEC sp_CompareDb  @SourceDB='DB1', @TargetDb ='DB2'

    Regards
    Satheesh

    Monday, June 25, 2012 5:10 AM
    Answerer
  • Thanks
    Monday, June 25, 2012 6:48 AM
  • Do you want to compare the data or structure as well? For structure I have been using SQLCompare tool from www.red-gate.com for years.

    For data, look at this one

    IF (SELECT COUNT(*) FROM Foo)
       <> (SELECT COUNT(*) FROM Bar)
    PRINT 'No'
    ELSE IF (SELECT COUNT(*)
               FROM Foo, Bar
              WHERE Foo.c1 = Bar.c1
                AND Foo.c2 = Bar.c2
                 ...
                AND Foo.cn = Bar.cn)
             = (SELECT COUNT(*) FROM Bar)
          AND (SELECT COUNT(*) FROM Foo)
             = (SELECT COUNT(*) FROM Bar)
         THEN 'Yes'
    END;


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Maggie Luo Sunday, July 8, 2012 4:29 PM
    Monday, June 25, 2012 7:12 AM
    Answerer
  • For data, look at this one

    IF (SELECT COUNT(*) FROM Foo)
       <> (SELECT COUNT(*) FROM Bar)
    PRINT 'No'
    ELSE IF (SELECT COUNT(*)
               FROM Foo, Bar
              WHERE Foo.c1 = Bar.c1
                AND Foo.c2 = Bar.c2
                 ...
                AND Foo.cn = Bar.cn)
             = (SELECT COUNT(*) FROM Bar)
          AND (SELECT COUNT(*) FROM Foo)
             = (SELECT COUNT(*) FROM Bar)
         THEN 'Yes'
    END;

    Nice and simple query, Thanks for sharing.

    I changed this query a bit to use INTERSECT for easy use

    IF (SELECT COUNT(*) FROM foo) <> (SELECT COUNT(*) FROM bar)
    	PRINT 'No'
    ELSE IF (SELECT COUNT(*)  FROM (SELECT * from foo INTERSECT SELECT * FROM bar)Tab1)
             = (SELECT COUNT(*) FROM Bar)
          AND (SELECT COUNT(*) FROM Foo)
             = (SELECT COUNT(*) FROM Bar)
         print 'Yes'
    ELSE
    print 'No'

    Regards
    Satheesh




    Monday, June 25, 2012 7:27 AM
    Answerer
  • Restore you backup database in the same/different server and use some comparison tools...

    http://www.adeptsql.com/ is free for non-commercial. If feel this a wonderful tool compare databases, create scripts and execute them.

    or

    If you at work and have Visual Studio 2010 Premium or Ultimate then you can use Microsoft Data Compare tool which is coming along with visual studio to compare two different databases.

    Hope this will be helpful!!!


    Monday, June 25, 2012 7:54 PM
  • If you want to compare databases against backups without restoring, you can try this SQL compare tool from ApexSQL. It has a free trial and you can decide whether to use Dev or Pro version of this tool.
    Tuesday, September 3, 2013 9:22 PM
  • Hi

    i want compare sql databse with old backup via T-SQL?


    you can use Red Gate Tool to compare data base structure or data and you can download crack for it from the below link ,after download flow read me file steps

    http://www.2shared.com/file/0uePvyNe/RG_online.html?

    then download trial version from 

    http://www.red-gate.com/products/sql-development/sql-compare/


    • Edited by Mahmoud Soufi Tuesday, September 3, 2013 9:50 PM
    • Proposed as answer by Neno farid Tuesday, September 3, 2013 10:15 PM
    Tuesday, September 3, 2013 9:46 PM