Question

Add date to SQL database backup filename

I'm using the below to backup a db from a SQL job. Can someone tell me how to add the current date to the output filename? Preferably in YYYYMMDD format.

BACKUP DATABASE [myDB] TO  DISK = N'\\myPath\myDB.bak' WITH NOFORMAT, INIT,  NAME = N'myDB', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO

Thanks!

 45  117928  45
1 Jan 1970

Solution

 54
DECLARE @MyFileName varchar(1000)

SELECT @MyFileName = (SELECT '\\ServerToSave\Path\MyDB_' + convert(varchar(500),GetDate(),112) + '.bak') 

BACKUP DATABASE [myDB] TO DISK=@MyFileName ...
2010-03-09

Solution

 24

If you want to include the date and time, so you can use:

DECLARE @MyFileName varchar(200)
SELECT @MyFileName='\\ServerToSave\Path\MyDB_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '.bak'
BACKUP DATABASE [myDB] TO DISK=@MyFileName ...

The 120 in the Convert gives you the yyyy-mm-dd hh:mi:ss(24h)

The REPLACE function is necessary because the filename can not have the : character.

2012-03-13