DECLARE @destinationpath NVARCHAR(1000) DECLARE @beginbackuptimestamp VARCHAR(20)SET @destinationpath = '\\SERVER-01\sqlbackupshare$\' SET @beginbackuptimestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#backupcommand%') DROP TABLE #backupcommand
CREATE TABLE #backupcommand (id INT IDENTITY(1,1), command VARCHAR(1000))INSERT INTO #backupcommand (command) SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @destinationpath + DB_NAME() + '_' + @beginbackuptimestamp + '_' + name +'.BAK''' FROM sys.filegroups --WHERE name <> 'NCIndexes' ORDER BY data_space_idDECLARE @intCounter INT SET @intCounter = 0DECLARE @intMaxId INT SELECT @intMaxId = MAX(ID) FROM #backupcommand
DECLARE @CurrentCommand VARCHAR(1000)
WHILE (@intCounter <= @intMaxId)
BEGIN SET @intCounter = @intCounter + 1
SELECT @CurrentCommand = command FROM #backupcommand WHERE id = @intCounter
DECLARE @beginbackuptimestamp VARCHAR(20)SET @destinationpath = '\\SERVER-01\sqlbackupshare$\'
SET @beginbackuptimestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#backupcommand%')
DROP TABLE #backupcommand
CREATE TABLE #backupcommand (id INT IDENTITY(1,1), command VARCHAR(1000))INSERT INTO #backupcommand (command)
SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @destinationpath + DB_NAME() + '_' + @beginbackuptimestamp + '_' + name +'.BAK'''
FROM sys.filegroups
--WHERE name <> 'NCIndexes'
ORDER BY data_space_idDECLARE @intCounter INT
SET @intCounter = 0DECLARE @intMaxId INT
SELECT @intMaxId = MAX(ID) FROM #backupcommand
DECLARE @CurrentCommand VARCHAR(1000)
WHILE (@intCounter <= @intMaxId)
BEGIN
SET @intCounter = @intCounter + 1
SELECT @CurrentCommand = command FROM #backupcommand
WHERE id = @intCounter
RAISERROR (@CurrentCommand, 10, 1) WITH NOWAIT
EXEC (@CurrentCommand)
END
GO