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
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.
2 Likes
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