Q:

SQL script backup

I'm trying to run a backup script, but it won't run.  I can't find the error. 
BACKUP DATABASE [METROEXPRESS] TO  
DISK = N'D:\BackupTest\MCI_'+convert(varchar(8),getdate(),112)+'' WITH NOFORMAT, 
NOINIT,  NAME = N'METROEXPRESS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, 
STATS = 10
GO

I got the right script now:

DECLARE @MyFileName varchar(50) SELECT @MyFileName = (SELECT
  'D:\BackupTest\MCI_' + convert(varchar(8),getdate(),112) + '.bak') 
  BACKUP DATABASE [METROEXPRESS] TO DISK=@MyFileName

A:

You cannot do any "inline" calculations on the backup file name. 
However, at least in SQL Server 2012 (where I tested it), you can create a variable of type NVARCHAR(200) and fill it and use it:
DECLARE @DiskFile NVARCHAR(200)
SET @DiskFile = N'D:\Temp\MCI_' + CONVERT(NVARCHAR(8), GETDATE(), 112) + N'.bak'

BACKUP DATABASE [METROEXPRESS] TO  
DISK = @DiskFile WITH NOFORMAT, 
NOINIT,  NAME = N'METROEXPRESS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, 
STATS = 10

I am not sure if that's a new feature in SQL Server 2012 - I was under the impression I had tried this on earlier versions and couldn't get it to work (back then) - but now it definitely does work.