Backups with filename macros

Greetings, I think this should have a simple answer (whether yes or no)
I would like to make backups to a file in which the filename contains the day of the week. I would assume I could use %d or something like that to do macro substition for the current DOW
Here is what I am doing (so that this makes more sense)
Right click on your database, All Tasks --> Backup Database..
In the "Destination" section, I am adding a Disk destination and specifying a filename, such as "db_monday.sqlbackup" and "db_tuesday.sqlbackup", then consequently setting the schedule to backup every Monday, Tuesday, etc. I would like to set the schedule to backup every day, but specify a macro in the filename to substitute the day of the week. I have tried putting in "db_%d.sqlbackup", but the backup then creates a file named exactly that
Is this possible to do? If so, how
Thanks in advance
HeggYou could build some dynamic SQL to do that, something like below.
declare @.cmd varchar(100)
set @.cmd='BACKUP DATABASE YOURDB TO DISK=''C:\temp\DB' +
datename(weekday,getdate()+1) + '.sqlbackup'''
exec (@.cmd)
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg|||MiniSQLBackup allows you to use macros for backup type, server, instance and
database names, and the current date time elements, in addition to
generating smaller and encrypted backup files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg