database maintenance plan using T-Sql and current_timestamp

hi can anyone help, i have a database maintenance plan which is set to
run hourly at the moment it overwrites the current database but i want
to append it with a current_timestamp, here is the T-SQL i am using
BACKUP DATABASE [company name] TO DISK = N'E:\Hourly
Backup\Company\Company backup- Hourly.BAK' WITH INIT , NOUNLOAD ,
RETAINDAYS = 1, NAME = N'company backup - Hourly', SKIP , STATS =
10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9', FORMAT ,
MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'company Hourly
Backup'
My systemdb maintenance plan automatically appends the datestamp to the file
name:
BACKUP DATABASE [model] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\model_db_200602190913.BAK' WITH INIT , NOUNLOAD ,
NOSKIP , STATS = 10, NOFORMAT
Yours does not appear to be doing so. Did you create it using the wizard?
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140358625.726394.236770@.f14g2000cwb.googlegr oups.com...
> hi can anyone help, i have a database maintenance plan which is set to
> run hourly at the moment it overwrites the current database but i want
> to append it with a current_timestamp, here is the T-SQL i am using
> BACKUP DATABASE [company name] TO DISK = N'E:\Hourly
> Backup\Company\Company backup- Hourly.BAK' WITH INIT , NOUNLOAD ,
> RETAINDAYS = 1, NAME = N'company backup - Hourly', SKIP , STATS =
> 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9', FORMAT ,
> MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'company Hourly
> Backup'
>
|||Set the device to a variable that has the timestamp appended to it like so:
DECLARE @.Device NVARCHAR(500)
SET @.Device = N'E:\HourlyBackup\Company\Company backup- Hourly_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
BACKUP DATABASE [company name] TO DISK = @.Device ...
Andrew J. Kelly SQL MVP
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140358625.726394.236770@.f14g2000cwb.googlegr oups.com...
> hi can anyone help, i have a database maintenance plan which is set to
> run hourly at the moment it overwrites the current database but i want
> to append it with a current_timestamp, here is the T-SQL i am using
> BACKUP DATABASE [company name] TO DISK = N'E:\Hourly
> Backup\Company\Company backup- Hourly.BAK' WITH INIT , NOUNLOAD ,
> RETAINDAYS = 1, NAME = N'company backup - Hourly', SKIP , STATS =
> 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9', FORMAT ,
> MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'company Hourly
> Backup'
>
|||Andrew,
Firstly many thanks but im still having problems could you look over
this T-Sql for me
DECLARE @.Device NVARCHAR(500)
SET @.Device = N'E:\Hourly Backup\company\company backup- Hourly_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
BACKUP DATABASE [Company] TO DISK = @.Device = N'E:\Hourly
Backup\Company\Company backup- Hourly_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK' WITH INIT ,
NOUNLOAD , RETAINDAYS = 1, NAME = N'Company backup - Hourly', SKIP ,
STATS = 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9',
FORMAT , MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'Company
Hourly Backup'
Any guidance would be very much appreciated.
Matt
|||You just need the "DISK = @.Device" not "Disk = @.Device = N'E:..."
Andrew J. Kelly SQL MVP
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140444907.948174.219680@.g14g2000cwa.googlegr oups.com...
> Andrew,
> Firstly many thanks but im still having problems could you look over
> this T-Sql for me
> DECLARE @.Device NVARCHAR(500)
>
> SET @.Device = N'E:\Hourly Backup\company\company backup- Hourly_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
>
> BACKUP DATABASE [Company] TO DISK = @.Device = N'E:\Hourly
> Backup\Company\Company backup- Hourly_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK' WITH INIT ,
> NOUNLOAD , RETAINDAYS = 1, NAME = N'Company backup - Hourly', SKIP ,
> STATS = 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9',
> FORMAT , MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'Company
> Hourly Backup'
> Any guidance would be very much appreciated.
> Matt
>
|||Andrew,
Many thanks that has done the trick if i wanted to add the time what
would i need to add?
Once again many Thanks
|||Use one of the other options for CONVERT(). See BooksOnLine for the
available formats.
Andrew J. Kelly SQL MVP
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140451723.194913.69430@.z14g2000cwz.googlegro ups.com...
> Andrew,
> Many thanks that has done the trick if i wanted to add the time what
> would i need to add?
> Once again many Thanks
>