schedule job backup

Hi there
I want to implement a job command at SQL Server 2000 that should allow the
following:
1. Do a backup to "xpto" database
2. the name generated by the backup should be:
21112006xpto.bak
22112006xpto.bak
..
21022007xpto.bak
After archiving 3 months the first backup craeted "21112006xpto.bak" should
be replaced by "21022007xpto.bak"
once i just want to stay the earlier 3 months.
Actually i use the following command:
BACKUP DATABASE [xpto] TO DISK = N'C:\Microsoft SQL
Server\MSSQL\BACKUP\xpto.BAK' WITH INIT , NOUNLOAD , NAME = N'xpto
backup', SKIP , STATS = 10, NOFORMAT
Can somenone help me or give some tips?...
[]
RickyHi
To create the file using a current date then you would need to use dynamic S
QL
e.g.
DECLARE @.cmd varchar(1000)
SET @.cmd = 'BACKUP DATABASE [xpto] TO DISK = N''C:\Microsoft SQL
Server\MSSQL\BACKUP' + CONVERT(char(8),GETDATE(),112) + 'xpto.BAK'' WITH
INIT , NOUNLOAD , NAME = N''xpto backup'', SKIP , STATS = 10, NOFORMAT'
EXEC (@.cmd)
This will give you are filename YYYYMMDDxpto.BAK. This is a better format as
the files can easily be sorted.
To delete older files check out:
http://realsqlguy.com/serendipity/a...th-The-Old.html
Alternatively a DTS package using an activeX script would be a way of
deleting old files see http://www.sqldts.com/default.aspx?292
John
"Ricky" wrote:

> Hi there
> I want to implement a job command at SQL Server 2000 that should allow the
> following:
> 1. Do a backup to "xpto" database
> 2. the name generated by the backup should be:
> 21112006xpto.bak
> 22112006xpto.bak
> ...
> 21022007xpto.bak
> After archiving 3 months the first backup craeted "21112006xpto.bak" shoul
d
> be replaced by "21022007xpto.bak"
> once i just want to stay the earlier 3 months.
> Actually i use the following command:
> BACKUP DATABASE [xpto] TO DISK = N'C:\Microsoft SQL
> Server\MSSQL\BACKUP\xpto.BAK' WITH INIT , NOUNLOAD , NAME = N'xpto
> backup', SKIP , STATS = 10, NOFORMAT
> Can somenone help me or give some tips?...
> []
> Ricky
>
>