Backing up SQL 6.5 data

Backing up SQL 6.5 data

Post by Robert Bolling » Sat, 26 Jan 2002 02:57:50



What is the best/fastest way to backup about five years of data in one
large database -- so that I have a backup for each year.  Does SQL
Server 6.5 have any built-in utilites that will make this task
relatively easy?

What I'd like to do is store the data dump for each year on sperate
CDs.  Thank you for any suggestions!

R Bollinger

 
 
 

Backing up SQL 6.5 data

Post by Erland Sommarsko » Sat, 26 Jan 2002 07:47:45



> What is the best/fastest way to backup about five years of data in one
> large database -- so that I have a backup for each year.  Does SQL
> Server 6.5 have any built-in utilites that will make this task
> relatively easy?

> What I'd like to do is store the data dump for each year on sperate
> CDs.  Thank you for any suggestions!

You backup a database at a time. With 6.5 you can also backup a single
table. So if you have data for each year in separate tables you could
do that. But it is not a very good idea to split up tables that way.

If you can afford no better medium for your back than CD-R, your best
bet is to back up each table separately, and then squeeze them into
different CD:s.

However a tape drive is not that expensive - or for that matter a 30 GB
IDE disk you you can put into the vault.

--
Erland Sommarskog, Abaris AB

SQL Server MVP

 
 
 

Backing up SQL 6.5 data

Post by Robert Bolling » Sun, 27 Jan 2002 01:48:40


Thanks for the input.  Besides backing up the entire database and then
deleting records from all years except the last two years , what is
the best way of reducing the size of the database so that it only has
the last two years of data.

R Bollinger



> > What is the best/fastest way to backup about five years of data in one
> > large database -- so that I have a backup for each year.  Does SQL
> > Server 6.5 have any built-in utilites that will make this task
> > relatively easy?

> > What I'd like to do is store the data dump for each year on sperate
> > CDs.  Thank you for any suggestions!

> You backup a database at a time. With 6.5 you can also backup a single
> table. So if you have data for each year in separate tables you could
> do that. But it is not a very good idea to split up tables that way.

> If you can afford no better medium for your back than CD-R, your best
> bet is to back up each table separately, and then squeeze them into
> different CD:s.

> However a tape drive is not that expensive - or for that matter a 30 GB
> IDE disk you you can put into the vault.

 
 
 

Backing up SQL 6.5 data

Post by Erland Sommarsko » Sun, 27 Jan 2002 08:41:50



> Thanks for the input.  Besides backing up the entire database and then
> deleting records from all years except the last two years , what is
> the best way of reducing the size of the database so that it only has
> the last two years of data.

And you are asking that about a database I don't anything about?

Sorry, this is an application-specific question. And just to show you,
let me tell you about a database where they had done this. This was a
database for a stock broker. Due to disk limitation, they had deleted
all transactions before 1998-01-01. Was this is a good thing?

Well, first define "transactions before 1998-01-01". In a context like
this, a transaction has four or five dates:
* The accounting date - the banking day the transaction was registered.
* The trade date - the day the transcation actually happened. Often the
  same as the accounting day.
* The settle date - the day when you pay for your instruments ang get them.
* The value date - the date from which the transaction affects the interest.
  The same date as the settle date, or the next banking day.
* The cancel date - the day the transaction was cancelled (because it had
  never occurred or was incorrectly registered).

They had retained all transactions that had any of the first four dates
in 1998; I don't recall about the cancels.

Now, in a system like this you have a concept of a "trade" which is simply
is all contract notes in the same stock on one single day. But there are
different rule for the value date buy and sell transactions, so when I got
hold of this database I found a lot of incomplete trades made on
1997-12-27.

But that was not all. Most trading is buying and selling stock, but some
kind of trades are part of a chain. Examples of that are future contracts
and instrument loan. Since we were to add instrument loans, I had to
write code to reconstruct notes as far back as in August 1996. In the
spring of 1999 when I made this exercise (because we were converting
the data to our system), these were loans that were still open.

Obviously, the people who had removed old data in this database had
not analysed the exact consequences.

Now, I don't know anything about your data, but chances are good that
you have similar things to take in regard.

--
Erland Sommarskog, Abaris AB

SQL Server MVP

 
 
 

1. SQL Server 6.5 Back ups

Folks,

I can't make "backup database" function work. I go through all the steps -
make it a complete database backup to a "Backup" folder on the hard drive
with the "overwrite" existing media option on, go to schedule, select all
settings I need, click on the "schedule" box. Once I click on "OK",
everything I set for the back up procedure goes away including the schedule
options.

What am I doing wrong?

Thanks in advance!

Konstantin

2. rs.Find

3. How to revert back from SQL 6.5 SP5 to SQL 6.5 SP4

4. VB6 Menu Editor

5. ACCESS 97 fron-end/SQL-Server 6.5 back-end app: table-locks when only displaying data

6. BCP & ISQL dlls

7. Attach SQL Server 6.5 dat file back to SQL Server

8. Help! Connection Refused.

9. Convert a SQL string from SQL 7.0 back to 6.5

10. Back-ups

11. Copyng Back Ups ?

12. Converting full database back-ups to Flat Files

13. scheduled back-ups