Post reply

How to backup the Xmldata table in sql server 2016

  • November 21, 2019 at 8:18 am

    #3699481

    Hi All,

    My table have 2000000 records and size 60GB .when i take backup it will take time 60 min My backu query is using INTO statemet

    Select id,name,xmldata INTO new_tablename from tablename

    How to reduce the time...please help me.

    Regards

    Pols

  • November 22, 2019 at 9:10 am

    #3699918

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • November 22, 2019 at 9:14 am

    #3699923

    If you're copying 60GB from one place to another, then it's not going to be quick - and how slow it is will depend on your hardware.  Do you really need to copy the whole table - can you not just merge the changes into what is already there?  Have you considered replication or something like that?

    John

  • November 22, 2019 at 10:48 am

    #3699952

    i am using same database that is duplicate of another table ....

     

    table don't have the any indexs it is use only transaction purpose like only insert statement when i create index insert is slow and deadlock the table.The same table i am using report generation that time report generation take time more that time i create backup table then i can create index and generate report fast.

  • November 22, 2019 at 1:00 pm

    #3699986

    Can the database be set to the BULK LOGGED recovery model?  If not, can we put this table in a new, single table database that can?

    Also, how up to date does the new copy of the table need to be?

    --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)

  • November 23, 2019 at 5:13 am

    #3700199

    I am using the query is

    SELECT id,name,xmldata INTO New_Table Name from TableName

  • November 24, 2019 at 8:15 pm

    #3700340

    polo.csit wrote:

    I am using the query is

    SELECT id,name,xmldata INTO New_Table Name from TableName

    Yep... you said that from the git.  Can you answer my previous questions so I can best help you do this?

    --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)

  • November 25, 2019 at 8:14 am

    #3700411

    Database is Full Recovery Model...it is not possible to change Bulk logged recovery model

  • November 25, 2019 at 8:19 am

    #3700420

    polo.csit wrote:

    Database is Full Recovery Model...it is not possible to change Bulk logged recovery model

    My other question, which has still been unanswered is... can we create another database for the new table you want and point a synonym at it so that we can use the simple recovery model for this "throw away" table

    --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)

  • November 25, 2019 at 11:27 am

    #3700531

    created new database it is also take time more....

  • November 26, 2019 at 8:19 pm

    #3701223

    Your backup, to a table in same database, is it for migration/upgrade purposes?

    (If so, maybe set database to single user mode, first.)

    Otherwise is it possible to use the builtin database backup?

  • November 27, 2019 at 1:39 am

    #3701288

    polo.csit wrote:

    created new database it is also take time more....

    Maybe the way you're doing it.  Answer my previous question, please.  You'll be amazed at how fast we can make stuff.  Better yet, you'll be amazed at how little impact it will have on the original database and how little log file it'll all create.

     

    --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)

  • November 27, 2019 at 1:42 am

    #3701289

    jonas.gunnarsson 52434 wrote:

    Your backup, to a table in same database, is it for migration/upgrade purposes?

    (If so, maybe set database to single user mode, first.)

    Otherwise is it possible to use the builtin database backup?

    Heh... nah... it's because they're having problems with reporting issues against a table with a lot of "transactions".  Look back in the posts.  We've already been through this with the OP.

    --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)

  • November 27, 2019 at 3:04 pm

    #3701565

    Database is full recovery model.when copy the table in single table also take time more and copy of the table is INTO statement.

  • November 27, 2019 at 3:32 pm

    #3701582

    polo.csit wrote:

    Database is full recovery model.when copy the table in single table also take time more and copy of the table is INTO statement.

    We keep going in circles.  I already know that.  I asked you if we could create another database and you responded that it would take too long and I'm telling you it won't.  If you would just answer my questions instead of prejudging them, maybe I could help you do this in literally milliseconds of down time.

    So... one more time...

    1. Are we allowed to create a working database that can be in the simple recovery mode
    2. If so, can we put the copy of the table there and use a synonym to point to it from the original database?

    --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 15 posts - 1 through 15 (of 15 total)

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