SQL Server – Proper Way to Store and Restore Full Table Data Outside Database

archivesql serversql-server-2005

I have a large SQL Server 2005 database that hasn't had the best maintenance over the years (I just took it over recently). There are around 100 tables that can be completely removed from the database, as well as some that just need data after a certain point removed.

My employer wants to keep the data, but not in a database, and wants to be able to restore it to that database if the need arises.

The smaller tables could of course be exported as Excel files, but many are larger than xls or xlsx would allow. I also thought of storing the data as .txt, .sql or .csv files, but I want to be certain that I'm doing this properly and won't end up with corrupted tables that can't be restored.

As far as querying the tables for the data, I can handle that. So my main question is, how do you properly store a table of data outside of a database?

Thanks,
Jamie

Best Answer

My employer wants to keep the data, but not in a database, and wants to be able to restore it to that database if the need arises.

If you want the data to be restored later, then best is to

  1. Script out schema of the tables that you wish to drop and save it as a sql script.
  2. BCP out the data (without using -n switch as -n is for native format which is not HUMAN readable) and store it in a safe place. If you want to save some space, you can also ZIP it.

how do you properly store a table of data outside of a database?

If you want to really store the data outside of database, then option 1 and 2 are the ones that I will go for.

Now, what I will still do is -

  • Make a copy of the current database (using backup / restore method).
  • Restore it with a database_Archive name
  • Remove the tables that you don't need to archive i.e. Keep only tables that you will be eventually dropping from the main database (as per your Boss's requirement).
  • Then do a one time shrink (since you will be getting rid of lot of data)
  • Backup the final archive database.

The benefit of this is that

  • whenever you need to archive the data, it will be easy.
  • You dont have to manage individual files.
  • the database will be backed up, just in case you need it for a later time.
  • You can query the archive database anytime you need.

Check out the hyperlinks as it has method, scripts and best practice to help your out.

Related Question