Are there any environment variables that can be used on the SQL Backup
Schedule ?
I would like it to do a daily backup, each day with a different name
(something like sqlbackup%day%).

Thanks.

getdate()?

This script is part of a larger backup script that I have used in the past.
You will need to change the drive letter and path to suit your environment.


DECLARE @dbname varchar(128),

@sqlstr varchar(500)

DECLARE CR_BackupDatabases CURSOR FAST_FORWARD FOR

SELECT name FROM master..sysdatabases WHERE NAME NOT IN ('tempdb') ORDER BY
NAME

FOR READ ONLY

OPEN CR_BackupDatabases

FETCH NEXT FROM CR_BackupDatabases INTO @dbname

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

SET @sqlstr = 'BACKUP DATABASE [' + @dbname + '] TO DISK =
''X:\MSSQL\BACKUP\' + @dbname + '_' +
convert(char(8),convert(int,convert(char(8),getdat e(),112))) + '.bak''' +'
WITH INIT'

exec (@sqlstr)

END

FETCH NEXT FROM CR_BackupDatabases INTO @dbname

END

CLOSE CR_BackupDatabases

DEALLOCATE CR_BackupDatabases




--
Keith Kratochvil


"Marius Kaizerman" <marius@kaizerman.org> wrote in message
news:%231jawAt9GHA.3352@TK2MSFTNGP03.phx.gbl...
> Are there any environment variables that can be used on the SQL Backup
> Schedule ?
> I would like it to do a daily backup, each day with a different name
> (something like sqlbackup%day%).
>
> Thanks.
>
>

Hi,

Schedule the backup using Maintenance plan using Enterprise manager [SQL
2000] or using SQL Server Management studio [SQL 2005].
In Maintenance plan automatically backup will be created based on
DBname_day_month_year_time.
You can also set a archival period.

Thanks
Hari

"Marius Kaizerman" <marius@kaizerman.org> wrote in message
news:%231jawAt9GHA.3352@TK2MSFTNGP03.phx.gbl...
> Are there any environment variables that can be used on the SQL Backup
> Schedule ?
> I would like it to do a daily backup, day with a different name (something
> like sqlbackup%day%).
>
> Thanks.
>
>