/*
Script : Backup all file group
Version : September 2012
Author : Sushil Rout
Web : http://sushildbasupports.blogspot.com
*/
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_id
DECLARE @intCounter INT
SET @intCounter = 0
DECLARE @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
-- display command being executed. using RAISERROR here to display immediately, unlike PRINT
RAISERROR (@CurrentCommand, 10, 1) WITH NOWAIT
-- do the backup
EXEC (@CurrentCommand)
END
GO
No comments:
Post a Comment