keep last three backups in a file.

Question

keep last three backups in a file.

Maciej Szymanski

Old Hand

Points: 343
More actions
October 10, 2003 at 2:37 am

#151945

Hi,
is it possible (using t-sql) to do full backup of a database to a file so that it keeps only last three full database backups, truncating the fourth?
i.e. i've a backup file with 3 full backups 1st - 2nd - 3rd. now i would like to do the 4th full database backup and would like to "delete" the first one. how to do this ...and is it reasonable?
Thx
Maciej Szymañski

With regards

Maciej Szymañski

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

quote:
is it possible (using t-sql) to do full backup of a database to a file so that it keeps only last three full database backups, truncating the fourth?
i.e. i've a backup file with 3 full backups 1st - 2nd - 3rd. now i would like to do the 4th full database backup and would like to "delete" the first one. how to do this ...and is it reasonable?

are you looking for the parameter EXPIREDATE in the BACKUP syntax?

Take a look at BACKUP (described) in BOL.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Maciej Szymanski Old Hand Points: 343 More actions · Answer 2

Not exactly. according to bol Expiredate parameter specifies the date when the _backup set_ expires and can be overwritten. In my case the _whole file_ (with 3 backups) is a backup set, so i think i cannot just overwrite the oldest backup with the new one.

With regards

Maciej Szymañski

With regards

Maciej Szymañski

Antares686 SSC Guru Points: 125444 More actions · Answer 3

First off I would do seperate files. The reason is if the file is damaged it effectively makes the whole thing useless.

Now as for you question

From BOL

quote:
{ DISK | TAPE } =
'physical_backup_device_name' | @physical_backup_device_name_var
Allows backups to be created on the specified disk or tape device. The physical device specified need not exist prior to executing the BACKUP statement. If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.
When specifying TO DISK or TO TAPE, enter the complete path and file name. For example, DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mybackup.dat' or TAPE = '\\.\TAPE0'.
Note If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under KEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer.
If using a network server with a Uniform Naming Convention (UNC) name or using a redirected drive letter, specify a device type of disk.
When specifying multiple files, logical file names (or variables) and physical file names (or variables) can be mixed. However, all devices must be of the same type (disk, tape, or pipe).
Backup to tape is not supported on Windows 98.

The disk file is a device or the media not a backupset. And taking into account from BOL

quote:
EXPIREDATE = { date | @date_var }
Specifies the date when the backup set expires and can be overwritten. If supplied as a variable (@date_var), this date is specified as either a string constant (@date_var = date), as a variable of character string data type (except for the ntext or text data types), a smalldatetime, or datetime variable, and must follow the configured system datetime format.

Which is for the Backup Set not the media.

So I tested and found that each gets it's own EXPIREDATE, however there is an issue even with INIT you cannot cause the expired set to be removed as it is. There is no option available to remove from the media a set that has any expiredate that has not been reached. So again you would be better with seperate files. Maybe setup three files using INIT and EXPIREDATE then you job can try file one and if fail try file 2 then 3 if needed. With the EXPIREDATE one is bound to be available.

Maciej Szymanski Old Hand Points: 343 More actions · Answer 4

quote:
First off I would do seperate files. The reason is if the file is damaged it effectively makes the whole thing useless.

Good point! It makes my idea of getting several backups into one file 'slightly' unsafe. I'll follow Your advice and do separete files.

quote:
The disk file is a device or the media not a backupset

yes. my mistake 🙂

Thanks!

With regards

Maciej Szymañski

With regards

Maciej Szymañski