CODEWITHSUNDEEP
sql-serverdatabasesql-server-2012viewdatabase-backups
Neru-J
Neru-J

Reputation: 1623

SQL Server 2012: Copy old data from "Production" to "Archive"-db each month

What's the best way to move data from production tables data to an archive tables data where the tables are similar

I have a SQL Server 2012 database with some "big data" tables that grows every seconds/minute. So to prevent my database-table growing to big making slower requests in the future. I want to move older data from:

"Production" db tables -> to another "archive" db tables

I have been looking into views where I could update a "Status"-field to have either [A] Production-db or [B] Archive-db. This way I can easily update older data with "Status" = B when I don't want to have A-data longer.

  1. But is this approach the best one? Because it seems it's only in the same database they can work.

  2. And what should I do if I got 2 databases?

  3. Should I write a script with SELECT INSERT DELETED?

  4. Does SQL Server have some built-in scripts/schedules where data isn't deleted and only some data is transferred not all?

It's not important the transferring data is fast, because it would probably happen one time per month.

Thanks

Upvotes: 1

Views: 5513

Answers (2)

ZaidiSarifuddin
ZaidiSarifuddin

Reputation: 11

By using 'insert to' statement, where you select desired old data to archived, you can easily achieved this. This is manual way, in which you can convert into a stored procedure based on your archiving condition. 

insert into *destinationArchiveDB*
    select * 
    from *sourceProductionDB*
    where *your archive condition*

For example:

insert into TRC_ARC.dbo.Table1
    select * 
    from TRC.dbo.Table1
    where ProductionDate = 'yyyy-mm-dd'

Keep in mind that the table structure for source and destination should be the same, if different SQL will show error.

Upvotes: 1

saso
saso

Reputation: 802

The easiest way to do this is to create a SQL Server Agent job (schedule it as you wish, e.g. once a month) and then move the data from production to archive database (using a stored procedure that is executed in the SQL Server agent job and first does INSERT into archive DB and DELETE from source DB).

If you need mode advanced ETL handling (like row-level error control, etc.), you can also create SSIS (SQL Server Integration Services) package and run the package via SQL Server Agent. But this is probably an overkill in your situation.

Upvotes: 1

Related Questions