Sql-server – How to archive a whole database

archivebackupsql serversql server 2014

I have a database that is not going to be used anymore but that needs to be kept around for a long time. I'd like to remove it from the production system. My goal is to save storage space when archiving that database. I need to preserve it at full fidelity, so a BCP export is not good enough. I also would not trust generated SQL scripts to preserve it fully.

Two options:

Take an uncompressed backup and compress it using 7-Zip.
Detach the database and compress the MDF and LDF files using 7-Zip.

Is option 2 safe? Which one is preferable? What are important considerations here?

Best Answer

You can save the index creation DDL statement and delete the indexes, to save more space before any archival path you choose. There's more advice on shrinking and fill factor here: http://www.brentozar.com/archive/2010/02/how-to-really-compress-your-sql-server-backups/

I've used option 2 to get better results than with the SQL built-in backup compression. Always start with DBCC CHECKDB ! Also take into consideration that your restore time will go up if you go on this path, as it takes a while to (de)compress with 7-Zip's best compression settings.

Related Solutions

PostgreSQL – Database Archive Solutions

My suggestion about archiving:

Create archive_tablespace (if you want you can separate hardware on archive)
Create tables. For example we want to archive table posts.
```
create table  posts_all ( LIKE public.posts)  ;
create table  posts_archive () inherits  ( public.posts_all)  ;
alter table  public.posts  inherits ( public.posts_all ) ;
```
After that we will have 2 new tables: public.posts_all (with the same columns like in posts) to query all posts (archive and production) and public.posts_archive to query all archive posts. Public.posts will inherits from posts_all.
Inserts should go in a old way (to table public.posts) unless you will write triggers on posts_all to redirect inserts to posts table. If you have partitioning it will be more complicated. With working application and before old data migration you don't have to change anything in application code to work with this approach.
Create schema archive for logical separation. My suggestion will be to separate archive data by some time period (year or month) if possible (archive_2005).
Create archive tables in archive_year schema
```
create table archive_2005.posts (
  check(record_date >= '2005-01-01 00:00:00'::timestamp 
    and record_date <  '2006-01-01 00:00:00'::timestamp)
) inherits (posts_archive) tablespace archive_tablesapce;
```
After that you will have new table posts in schema archive_2005 and postgresql planer will know that data there is only in designed time period. If you query by another time period postgresql will not search in this table.
Create functions/procedures/triggers to move data to archive tables.
Do archive once for a time period (year here) and vacuum old table or do it automatically by triggers (heavier on autovacuum). There are many advantages and disadvantages in both techniques.

If implemented:

Can query archive (select * from posts_archive), all (select * from posts_all) and production (select * from public.posts) data separately
Can do dump archive schemas separately and drop cascade on them in easy way. pg_dump -s archive_2005 datase_name drop schema archive_2005 cascade; --be careful because it removes all related tables
Old data separated physically by tablespace and logically by schema.
Quite complicated structure to manage archiving process
Can create different indexes on production and archive tables to optimize queries to both (smaller and specialized indexes = faster queries and less space required)
If you have partitioned tables (by year or month) archive process will be just to move whole table to archive_tablespace or just alter it to inherit from posts_archive (I didn't test this)
If you don't want to access old (archived) data you don't have to change anything in application.

This is general technique and you should adapt it to your needs. Any suggestions to improve this?

Further reading: PostgreSQL inheritance, partitioning

Postgresql – Archive database on Postgres

I recently answered similar question here: database-archive-solutions

You should read about postgresql table partitioning, if you have good way to split data in parts.

Best Answer

Related Solutions

PostgreSQL – Database Archive Solutions

Postgresql – Archive database on Postgres

Related Question