T-SQL Code to backup database with multiple files failed

Question

I'm trying to write an automated script that will allow me to backup the database to a NAS with multiple files. I just started to use T-SQL.

My objective is to automatically calculate the size of the database and divide it by 4 (4 GB), the result will be the numbers of files of the database.

Example:

Database Size: 60 GB File Size: 4GB (The size of the multiple files)

Then: 60 GB / 4 GB = 15 Files for the backup

I guess is not possible to do it with a FOR or WHILE due to the code in T-SQL.

So I have been trying to run the code below but it returns with the following error:

  declare @DBName varchar(100)
declare @DBFileName varchar(256) 
declare @FolderName varchar(256)
declare @Path varchar(100) 

set @Path = '\\Backup-Server\Test\'
set @DBName = 'DayNite'

set @DBFileName = 'DayNite-Full' + '-' + (SELECT CONVERT(char(10), GetDate(),110)) + '-' + 'P'
set @FolderName =(SELECT CONVERT(char(10), GetDate(),110))
set @Path = @Path + @FolderName + '\'


EXEC master.dbo.xp_create_subdir @Path

--Calculate broken files  for BACKUP DATBASE Function
/*declare @dbsize int
set @dbsize = (SELECT ((size*8)/1024)/1000 as SizeGB FROM sys.database_files WHERE file_id = '1')
set @dbsize = @dbsize / 4
print @dbsize*/

BACKUP DATABASE [test] TO
DISK = @Path + @DBFileName + '1.bak',
DISK = @Path + @DBFileName + '2.bak',
DISK = @Path + @DBFileName + '3.bak',
DISK = @Path + @DBFileName + '4.bak',
DISK = @Path + @DBFileName + '5.bak',
DISK = @Path + @DBFileName + '6.bak',
DISK = @Path + @DBFileName + '7.bak',
DISK = @Path + @DBFileName + '8.bak',
DISK = @Path + @DBFileName + '9.bak',
DISK = @Path + @DBFileName + '10.bak',
DISK = @Path + @DBFileName + '11.bak',
DISK = @Path + @DBFileName + '12.bak',
DISK = @Path + @DBFileName + '13.bak'
WITH INIT , NOUNLOAD , NAME = 'DayNite Full Backup', NOSKIP , NOFORMAT)

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '+'.
Msg 319, Level 15, State 1, Line 30
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.

I don't know what is wrong.

score 0 · Answer 1 · answered May 19 '15 at 14:47

I would recommend Ola's solution for backup and specifying the number of files like so :

DECLARE @DBFiles INT 
SET @DBFiles = 
(SELECT 
total_size_mb = CAST(SUM(size) * 8. / 1024 / 1024  AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id
)/4 + 1

and then execute Bla's script with the number of files specified :

exec [DatabaseBackup] @databases = 'test', @directory = @Path, @NumberOfFiles = @dbfiles

However if you would like your own custom script you would need to use Dynamic SQL - something like the example below :

DECLARE @DBNAME VARCHAR(100)
DECLARE @DBFileName varchar(256) 
declare @FolderName varchar(256)
declare @Path varchar(100) 
set @Path = '\\Backup-Server\Test\'
set @DBName = 'DayNite'
set @DBFileName = 'DayNite-Full' + '-' + (SELECT CONVERT(char(10), GetDate(),110)) + '-' + 'P'
set @FolderName =(SELECT CONVERT(char(10), GetDate(),110))
set @Path = @Path + @FolderName + '\'
--You would probably need to format your query as dynamic sql: 
DECLARE @DSQL NVARCHAR(MAX)
DECLARE @DBFiles INT 
DECLARE @i INT = 1
SET @DBFiles = 
(SELECT 
total_size_mb = CAST(SUM(size) * 8. / 1024 / 1024  AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id
)/4 + 1
SET @DSQL = 'BACKUP DATABASE [test] TO ' 
WHILE (@i <= @DBFiles)
BEGIN 
SET @DSQL += 'DISK =''' +@Path + @DBFileName + CAST(@i as nvarchar(10)) + '.bak'', '
SET @i += 1 
END 
SET @DSQL = SUBSTRING (@DSQL, 1, len(@dsql) - 2)
set @DSQL += 'WITH INIT , NOUNLOAD , NAME = ''DayNite Full Backup'', NOSKIP , NOFORMAT)'
-- print for debug 
PRINT @DSQL
-- execute 
exec sp_executesql @dsql

I hope this helps.

T-SQL Code to backup database with multiple files failed

1 Answers1