I am looking for the best way to backup and remove old data.
I have a large SQL database that is over 90gigs.
I am using SQL Server 2000.
I have years of data that I want to back up and remove from the original database to free up space and hopefully improve performance. I have information logged from 2005. I am hoping to backup each year from 2005 to 2007 then start doing monthly backups and removing what I backed up.
I have tried backing up data using the text file option and default settings (Double Quotes,CSV). The file that is exported when zipped is awesome! I compressed a 9gig file down to 900meg. I have unzipped the file, and attempted a restore to a test database to verify that there will be no problems. It is cumbersome because the CSV file does not hold the type information of each column. This needs to be re-entered on the table after it is restored.
The next method that I just got done trying was to export to another database on the same server, disconnect the database and backup the MDF and LDF files. I have just tried to backup these files and expected the same results but the zip file is actually a little bit larger then the 2 files combined.
I am curious if anyone else has to do something similar? What the best method would be to backup SQL data to be removed and restored at a later date if it is needed?
Thanks,
I have a large SQL database that is over 90gigs.
I am using SQL Server 2000.
I have years of data that I want to back up and remove from the original database to free up space and hopefully improve performance. I have information logged from 2005. I am hoping to backup each year from 2005 to 2007 then start doing monthly backups and removing what I backed up.
I have tried backing up data using the text file option and default settings (Double Quotes,CSV). The file that is exported when zipped is awesome! I compressed a 9gig file down to 900meg. I have unzipped the file, and attempted a restore to a test database to verify that there will be no problems. It is cumbersome because the CSV file does not hold the type information of each column. This needs to be re-entered on the table after it is restored.
The next method that I just got done trying was to export to another database on the same server, disconnect the database and backup the MDF and LDF files. I have just tried to backup these files and expected the same results but the zip file is actually a little bit larger then the 2 files combined.
I am curious if anyone else has to do something similar? What the best method would be to backup SQL data to be removed and restored at a later date if it is needed?
Thanks,