I'm having trouble with a script... I'm fairly new at SQL Server, but determined to get better.
The Purpose of my script is to take a database, back it up to a file without a time/date stamp on it. I want to do this so I can use an SFTP script to push the file to one of my customers on a weekly basis. The SFTP script being used has a requirement for the file to be the same name for it to automatically run each Saturday. The script is below... i am getting this error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'E'.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Can someone point me in the right direction? I must be missing something easy. The directory does exist. Why is it having a problem with the E for my E drive, and what is wrong with my WITH. (This is SQL 2005 Std).
DECLARE @Filename varchar(256)
DECLARE @BkpPath varchar(256)
DECLARE @DBName varchar(256)
SET @BkpPath = 'E:\SQL Backups\TEST\'
SELECT @DBName = 'MyDatabase'
SELECT @FileName = @DBName + 'Backup.BAK'
--SELECT @BkpPath = 'E:\SQL Backups\TEST\'
SELECT @FileName = @BkpPath + @FileName
DECLARE @SQL varchar(max)
SELECT @SQL =
'BACKUP DATABASE ' + @DBName + ' TO
DISK ='+ @FileName
WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD, STATS = 10'