Best practice for archiving old data? We are migrating 3 slightly different SQL databases into one (SQL Server 2008R2). We want to keep old databases "alive" for some period of time but once we've gone live we don’t want staff accidentally going into old database, so taking the database offline for awhile then drop it would be the solution? taking backups for it and test it in another serve prior to do this of course.

I’m not sure what best practice is for making archives, but suspect that making SQL server backups and placing on tape may be a solution.  Do you know if SQL Server backups are compressed and can be saved to tape?  There may be better solutions than tape these days, but I think tape is still the standard.

Thanks for any advice
We want to keep old databases "alive" for some period of time but once we've gone live we don’t want staff accidentally going into old database, so taking the database offline for awhile then drop it would be the solution?


Instead of making database OFFLINE, make the database "READ ONLY" using :

ALTER DATABASE database_name SET READ_ONLY


And monitor what apps/ users are still connecting using server side trace.


  Do you know if SQL Server backups are compressed and can be saved to tape? 


Yes, backup compression is available in sql server 2008 R2 standard edition and up.

Enable backup compression :

EXEC sp_configure 'backup compression default', 1 ;
RECONFIGURE WITH OVERRIDE ;


When taking backups, use BACKUP DATABASE ... with COMPRESSION.

You can save the backups to tape. Thats what we do in our company as well for data retention (We have to retain data for 10+ years). Alternatively, you can use Windows Azure Blob Storage Service.

Make sure that the archive bit is set (attribute 'A' on the backup file).