Database Backup

[whloo]

Hi,

Currently i schedule my SQL sever agent to do database backup once in a few days with the default backup file name to ABC. Hence everytime, it will overwrite the previous file. Is there a way for me to auto append date time at the back of my default file name? Eg will be ABC_DD_MM_YYYY
Thanks!

Regards,
weihann.

 

[nigelrivett]

Sure - create the filename using getdate()

see

http://www.nigelrivett.net/BackupAllDatabases.html

You won't want all of this - just the parts to set the backup file name and delete old versions maybe.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[whloo]

hi nigelrivett,

thanks for your fast reply.
i am using enterprise manager to do my database backup.
hence, i don't know where to add those code
Sorry.... i am just a beginner in these....
thanks!

Regards,
weihann.

 

[nigelrivett]

easier to do it from query analyser

backup database mydb to disk = c:\mydb_full_20040217_1339.bak

Something like that - have a look at the backup database command in bol.

Do you mean you have created a maintenance plan (yuk)?
I thought that included the date in the filename by default.

Instead just schedule a job which contains
something like

declare @sql
select @sql = 'backup database mydb to disk = c:\mydb_full_' + convert(char(9),getdate(),112) + replace(convert(varchar(8),getdate(),108),':','')+ '.bak
exec (@sql)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SSesham]

i agree with nigelrivett but just a little change in quotes and u can paste this code as a job and schedule it using SQL Server Agent\Jobs in Enterprise Manager!!

declare @sql
set @sql = 'BACKUP DATABASE mydb TO DISK = ''\\Myserver\sqlbackup\mydb_' + convert(char(9), getdate(), 112) + replace(convert(varchar(8), getdate(), 108), ':', '') + '.bak'''

exec (@sql)