Change the drive letter and the database name as needed. This will place a file named with the database and today’s date.
declare @backupdatabase varchar(1024);
declare @backuplocation varchar(1024);
declare @todaysdate varchar(1024);
SELECT @todaysdate = CAST(YEAR(GETDATE()) as varchar)
+ RIGHT(’00’ + CAST(MONTH(GETDATE()) as varchar), 2)
+ RIGHT(’00’ + CAST(DAY(DATEADD(DAY, -1, GETDATE())) as varchar), 2);
SELECT @backupdatabase = ‘mydatabasename’;
SELECT @backuplocation = ‘F:\’ + @backupdatabase + ‘.’ + @todaysdate + ‘.BAK’;
BACKUP DATABASE @backupdatabase TO DISK = @backuplocation WITH NOFORMAT, NOINIT, NAME = N’Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10;