Post reply

How to backup only new data?

  • February 15, 2012 at 10:13 am

    #256867

    Hi folks,

    I have privilege to a table on a database in remote server, the table has 20+ fields and over 70k records.

    I need to back up them to my local, I want only the new data.

    Currently I do select into, but that means difficult to filter out redundant data.

    What's the easiest way to do this?

    THanks.

  • February 15, 2012 at 10:20 am

    #1447419

    The only way to do this is if you have a way of marking what new data is. Perhaps some column that marks when things have changed.

  • February 15, 2012 at 10:23 am

    #1447421

    You will have to download the whole table and compare it to your local data, unless you have something on the remote table that you could use to filter data (something like a last_modified" column).

    70K records is not that much. Why are you concerned about the size?

    -- Gianluca Sartori

  • February 15, 2012 at 10:02 pm

    #1447645

    halifaxdal (2/15/2012)

    Hi folks,

    I have privilege to a table on a database in remote server, the table has 20+ fields and over 70k records.

    I need to back up them to my local, I want only the new data.

    Currently I do select into, but that means difficult to filter out redundant data.

    What's the easiest way to do this?

    THanks.

    There's a super easy way to do this with the caveat that "easiest" isn't always the "fastest" or the "best". Create a unique index with the IGNORE DUPLICATES option and simply do inserts.

    In SQL Server 2008, I'd suggest using a MERGE. Since this is 2005, you might want to look into EXCEPT.

    Pretty much no matter what you do, you still have to deal with the remote server and it's likely to look at a lot more data in the remote table than you'd like. It may be a fair bit faster (it was for me) so simply do a SELECT/INTO a temp table and then do an EXCEPT comparison between the two to mark the rows for insert without duplication.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 16, 2012 at 5:51 am

    #1447792

    I'd cheat.

    Get a copy of Red Gate SQL Compare and just capture the data that exists in one database, but not the other. Very easy that way. But then, I'm lazy.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 16, 2012 at 6:47 am

    #1447834

    Grant Fritchey (2/16/2012)

    I'd cheat.

    Get a copy of Red Gate SQL Compare and just capture the data that exists in one database, but not the other. Very easy that way. But then, I'm lazy.

    I'm lazy my own self so let me ask... Can it be scheduled as a standalone run?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 16, 2012 at 6:49 am

    #1447835

    Jeff Moden (2/16/2012)

    Grant Fritchey (2/16/2012)

    I'd cheat.

    Get a copy of Red Gate SQL Compare and just capture the data that exists in one database, but not the other. Very easy that way. But then, I'm lazy.

    I'm lazy my own self so let me ask... Can it be scheduled as a standalone run?

    Yep. It has a command line and you can tell it what you want done and how, then schedule it with SQL Agent or whatever.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 16, 2012 at 8:45 am

    #1447951

    Jeff Moden (2/16/2012)

    Grant Fritchey (2/16/2012)

    I'd cheat.

    Get a copy of Red Gate SQL Compare and just capture the data that exists in one database, but not the other. Very easy that way. But then, I'm lazy.

    I'm lazy my own self so let me ask... Can it be scheduled as a standalone run?

    You mean SQL Data Compare right?

  • February 16, 2012 at 8:53 am

    #1447964

    Red Gate is not an option: it gives me error that my current privilege is not enough to even compare data using Red Gate SQL Data Compare:

    EXECUTE permission denied on object 'sp_dbcmptlevel', database 'master', owner 'dbo'

  • February 16, 2012 at 10:09 am

    #1448029

    Yes, sorry, SQL Data Compare, you're right.

    Hmmm... I think you need to view server state to make that work.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 16, 2012 at 4:10 pm

    #1448219

    Grant Fritchey (2/16/2012)

    Jeff Moden (2/16/2012)

    Grant Fritchey (2/16/2012)

    I'd cheat.

    Get a copy of Red Gate SQL Compare and just capture the data that exists in one database, but not the other. Very easy that way. But then, I'm lazy.

    I'm lazy my own self so let me ask... Can it be scheduled as a standalone run?

    Yep. It has a command line and you can tell it what you want done and how, then schedule it with SQL Agent or whatever.

    VERY interesting. That'll work with both the SQL Compare and the Data Compare products, yes? That might just let me pull of a small automation miracle in the next couple of months if I can get the project scheduled.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 16, 2012 at 4:12 pm

    #1448220

    halifaxdal (2/16/2012)

    Red Gate is not an option: it gives me error that my current privilege is not enough to even compare data using Red Gate SQL Data Compare:

    EXECUTE permission denied on object 'sp_dbcmptlevel', database 'master', owner 'dbo'

    Using that command line option that Grant spoke about, you just might be able to pull this off. Have someone create a job for you that uses the command-line option of the compare and give you privs to run it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply