change backup file name with current date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

change backup file name with current date

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-07-07 : 15:11:28

I have a job that runs following backup statement:
BACKUP DATABASE TIME
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\RAIWeb\TIME.bak' WITH INIT

I would like to rename the backup fille with current date at the end during the backup . For example, Time070706.bak
How can I do this dynamically?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-07 : 15:36:53

DECLARE @s varchar(100)

SET @s = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\RAIWeb\TIME' + REPLACE(CONVERT(varchar(10), GETDATE(), 1), '/', '') + '.bak'

BACKUP ...
TO DISK = @d

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-07 : 15:36:59

[code]

declare @bk_file varchar(1000)

set @bk_file =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\'+
'MSSQL\Backup\RAIWeb\TIME_'+
convert(varchar(30),getdate(),112)+
'.bak'

backup database TIME
to disk = @bk_file
with init

[/code]

CODO ERGO SUM

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-07-07 : 20:43:37

thanks, one more question
how can I delete the backup file from the previous date before the current backup.

For example, I've like to delete time070606.bak file before backing up time070706.bak file

Do I have use xp_cmdshell command?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-07 : 20:54:04

yes. you can use xp_cmdshell to delete it

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-07-08 : 00:11:13

can you show me some examples?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-08 : 00:18:24

Check out my backup stored procedure on my blog:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

It is thoroughly commented, so you should be able to find the section that performs the deletion of old files.

Tara Kizer
aka tduggan

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-07-13 : 12:41:07

Tara,

If I run your isp_backup SP,
I am getting an error:

Msg 3201, Level 16, State 1, Procedure isp_Backup, Line 159
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backupaspnetdb\aspnetdb_20060713113616.BAK'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Procedure isp_Backup, Line 159

I am not sure why i am getting this error because the file path is correct.. I am trying to save the backup file to :C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-13 : 12:47:44

Try saving it to a location that doesn't have spaces in the folder names. I don't believe I included code for the spaces. We never backup to folders that have spaces, so I doubt I considered that.

Our backups go to:

DriveLetter:\MSSQL\Backup
Where DriveLetter is never the C drive.

Tara Kizer
aka tduggan

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-13 : 13:21:30

Have a look at
http://www.nigelrivett.net/SQLAdmin/BackupAllDatabases.html

It will backup all databases on a server, include the date/time in the backup file name and delete old files.
All configurable from a table of database names.
If you just want one database hard coded then it's easy to change.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources