SQLTeam.com | Weblogs | Forums

Command format not recognized on sql 2008

Hi to all
I have a sql 2008 r2 std and Im trying to execute a job to generate a backup with personalized name but I received an error with the command format, is not recognized, my code runes well on sql 2014 but not in 2008 how can I modified it to run? this is my code :

--Script 1: Backup specific database

-- 1. Variable declaration

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @pathwithname VARCHAR(500)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)

-- 2. Setting the backup path

SET @path = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.VKSQL2008\MSSQL\Backup'

-- 3. Getting the time values

SELECT @time = GETDATE()
SELECT @year = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))

-- 4. Defining the filename format

SELECT @name ='006' + '_' + @year + @month + @day

SET @pathwithname = @path + @namE + '.bak'

--5. Executing the backup command

BACKUP DATABASE [006]
TO DISK = @pathwithname WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10

thanks in advance

Declare @backup varchar(max)

Select @backup =

' BACKUP DATABASE [006]
TO DISK = ' + @pathwithname + 
' WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'

Print @backup ---to see if it is a clean script
Exec(@backup)

FORMAT was introduced in SQL Server 2012 and is not available in 2008. You can simplify this whole expression using something like this:

SELECT @filename = @path + '\006_' + _ convert(char(8), getdate(), 112) + '.bak';

This will give you a file name as 006_YYYYMMDD.bak

I would suggest expanding the convert to include the time - especially for transaction log backups and differential backups. Something like:

SELECT @filename = '\006_' + replace(replace(replace(convert(varchar(20), getdate(), 120), '-', ''), ':', ''), ' ', '') + '.bak';

And better yet - place each databases backups in their own folder:

    Set @databaseDirectory += @database + '\';
Execute xp_create_subdir @databaseDirectory;

And then use this:

SELECT @filename = @databaseDirectory + '\006_' + replace(replace(replace(convert(varchar(20), getdate(), 120), '-', ''), ':', ''), ' ', '') + '.bak';

And instead of hard-coding your path - use this to get the defined default backup directory for that instance:

        Declare @path nvarchar(4000);
        
        Execute master.dbo.xp_instance_regread 
                N'HKEY_LOCAL_MACHINE'
              , N'Software\Microsoft\MSSQLServer\MSSQLServer'
              , N'BackupDirectory'
              , @path output
              , 'no_output'