☰

T-SQL 代码备份包含多个文件的数据库失败

tag-icon • tag-icon sql-server sql-server-2005 tsql
T-SQL 代码备份包含多个文件的数据库失败

我正在尝试编写一个自动化脚本，以便我将数据库备份到包含多个文件的 NAS。我刚开始使用 T-SQL。

我的目标是自动计算数据库的大小并将其除以 4（4 GB），结果就是数据库的文件数量。

例子：

数据库大小：60 GB 文件大小：4GB（多个文件的大小）

然后：60 GB / 4 GB = 15 个文件用于备份

我猜测由于 T-SQL 中的代码，无法使用 FOR 或 WHILE 来执行此操作。

因此，我一直尝试运行下面的代码，但它返回以下错误：

  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.

我不知道什么地方出了问题。

答案1

我推荐 Ola 的解决方案来进行备份并指定文件数量，如下所示：

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

然后使用指定的文件数量执行 Bla 的脚本：

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

但是如果您想要自己的自定义脚本，则需要使用动态 SQL - 类似于下面的示例：

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

我希望这有帮助。

相关内容