Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185

    Unanswered: Sql Server 6.5 Backup...

    I need to run a backup command on SQL Server 6.5, with the option to delete the backup files, older than 3 days. I'm running the command below:
    dump database ABC
    TO ABC_DEVICE
    WITH
    NOUNLOAD,
    STATS=10,
    INIT,
    NOSKIP

    The above command is overwriting everytime it starts fresh. Is there a way to keep the backup which are 3 days older and still write to the same backup device?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Nope, not even in 2K. Instead look into SQLMAINT. Having 3 days worth of backups in 1 file is 3 times more dangerous than having them 1 in each individual file. In 6.5 the GUI gives you an erroneous warining about danger to run SQLMAINT against databases greater than 400MB, - just ignore it (as per M$ and 6 years of running it myself.)

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    I use a script for my 2000 databases that write a complete backup for each day of the week. We have a backup server with 7 drives with shares named for each day, which later get backed up to tape. Next week, the backups to disk are overwritten.

    My script sets a variable to the day of week:

    SET @day_of_week=DATENAME(dw, GETDATE())

    then I specify the backup location explicitly:

    SET @db_location_string='\\backupservername\' + @day_of_week + '\SQL\' + @@SERVERNAME + '\' + @database_name + '.bak'

    BACKUP DATABASE @database_name TO DISK = @db_location_string WITH NOINIT , NOUNLOAD , NAME = @database_name, NOSKIP , STATS = 10, NOFORMAT

    Not sure if this comand will be 6.5 compatible, but something like it might be possible.
    -bpd

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    bpdWork,

    You're writing to an individual file until 7 days worth of backups are accumulated, after which you re-initialize the device. sqlserver2k is trying to append to the same backup device without completely overwriting it, but rather "push out" the 4-day-old backup from the file (did I understand it correctly?) As I said earlier, SQLMAINT with -DelBkUps will ensure that backups older than the specified number of days/weeks will be retained, and the rest - deleted.

  5. #5
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    No. Each day of the week I am writing a complete backup. On Monday, for example, I will overwrite last Monday's backup.

    We perform backup's to tape from these non-production disk backups. We handle our other backups (file, server, etc) this way as well.

    At any time a have complete backups available from disk for the previous 7 days, plus long term tape.
    -bpd

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    On many of our servers we switched to SQLLiteSpeed (of course we already upgraded all our 6.5's, or rather the ones we care about, to 7.0 or 2K) and have a retention period on disk for up to a month, instead of 4 days max using SQLMAINT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •