4

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

Colin 't Hart
  • 9,455
  • 15
  • 36
  • 44
Jamie T
  • 45
  • 1
  • 5

2 Answers2

3

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.

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
1

Not sure why your boss doesn't want to keep a backup of the DB on hand in the case of need of future restore, but I guess he has his reasons.

The nice thing about exporting this data out to text files is that you could always import them back into databases that aren't necessarily SQL Server in the future - as well as read them with text editors in a pinch. So, I would stick with a text file export.

Delimited Files:

CSV or some other delimiter is a pretty common way to go. There isn't a file size limit on the file... only the applications you might try to use to read the file. Putting a CSV back into SQL Server isn't a problem with tools like SSIS or even just the import/export wizard.

There are some nuances that might cause you to select a delimiter over the comma. For example let's say your tables contain strings with lots of commas. If that was the case you might pick pipe delimited to make it less confusing to work with.

If you have your heart set on comma delimited but you have strings with commas inside then there is an option when exporting to "qualify fields". Double quotes are often used in this case. So, for example a full name field that contains a comma but is exported to a comma delimited file would be qualified like this... "Thomas, Joe"

Fixed Width:

This is a popular export format when your fields are all fairly small. Fixed width can become a problem however if you have any (max) fields, binary, or other types that don't write well to text. Frankly, these data types don't export to delimited files very elegantly either.

Corruption:

Neither type of export to text are any more prone to corruption than another. You just have to be careful when people start opening them up in notepad, excel, or other random text editors and then save them... that is what can cause corruption. So be sure to keep a clean file backed up that you know no-one has messed with. But that brings us back to the original question... why not just keep a backup of the database?

RThomas
  • 3,446
  • 6
  • 30
  • 48