0

I have a super huge table (300+ million rows) that I need to back up frequently. I used a backup table to do so. Currently, the backup table is pretty much behind the main table, so I want to update my backup table. I want to copy over rows that exist on the main table and don't exist on the backup table yet. (The purpose of this table is to do some reporting purposes). I realize that it will take forever if I do something like this:

insert into backuptable select all from uptodatetable where not in select all from backuptable

so, I guess it will be a good idea to do it in a batch of rows. However I have never used batch before so can you guys help me? thank you

user2701646
  • 139
  • 4
  • 4
  • 20
  • Maybe you should look into replication. – Kermit Nov 19 '13 at 18:27
  • Similar: http://stackoverflow.com/a/12606336/61305 – Aaron Bertrand Nov 19 '13 at 18:29
  • HI Aaron, does it look like something like this? BEGIN TRANSACTION SELECT 1; WHILE @@ROWCOUNT > 0 BEGIN COMMIT TRANSACTION; INSERT INTO BACKUPTABLE (sClientId, sSerialBase, nCountry, sDate, sReleaseDate) SELECT TOP(1000) * FROM MAINTABLE WHERE NOT IN SELECT * FROM BACKUPTABLE END COMMIT TRANSACTION; – user2701646 Nov 19 '13 at 19:16

1 Answers1

0

How much change happens in your "super huge" table? Do you have a lot of inserts and deletes? A lot of updates? What is the purpose of your backup table? Do you need to be able to query it? Is what you really want more of a log of changes as they occur?

  • Assuming your data is important, you should have a backup plan running on your SQL Server instance that takes, say, a full backup of your database every night and incremental backups periodically through the day.

    The length of the period is dependent on how much data (in terms of transactions) you can afford to lose in case of failure. If you take incrementals every 60 minutes, you could theoretically lose an hour's worth of transaction if you have to recover from backup.

  • If you want a backup table that's an accurate mirror, for whatever reason, consider putting an insert/delete/update trigger on your table that mirrors the transaction on your backup table.

  • If you want a backup table that's essentiallyl an audit log of changes, create a backup table that mirrors the source table, but has some extra columns -- date/time of action, user who performed the action, action type (insert/delete: record an update as a delete followed by an insert). Doing this means you can conceivably recover to whatever point you want to by rolling back through the audit log performing the inverse operation. Or you can identify the guilty party and beat them about the head and shoulders. :D This approach takes a little thought but it's eminently doable.

  • You could also look at other solutions such as log shipping to a separate SQL Server instance for disaster recovery.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135