How to backup the xmldata table in sql server 2016

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.



one way is to ..!!!! do it little bit at a time
2,000,000 i.e 2 million

so 100,000 hundred thousand records at a time !!!

see if the below link helps !!!

it is ok.
but i want another new table backup.
the purpose is the main table have a so may transaction that time i am not running any select query when i take backup table the i can run my report query .

1 Like

any other options

Yes... there are tons. But first, tell us why you want a backup of this table in the form of another table. What is the real business or logical reason for doing such a thing?

I can't speak for anyone else but the reason I need to know this is because it can seriously impact what the best way to do this is.


Why not use SQL server backup out of the box. Is this Microsoft sql server

That's a part of where I'm going with this. We just don't know yet because we don't truly know what the real purpose of possibly having identical table content is.

1 Like

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.

You need to create a database dedicated for reporting purposes aka data mart aka data warehouse.
Initial load will be the longest then you just do update changed source rows, insert new source rows and delete (soft or hard) deleted source rows

it is also take time more

I am using the query is

SELECT id,name,xmldata INTO New_Table Name from TableName