Thursday, September 27, 2012

Backup all file group


/*
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: