Technical Article

Backup all Filegroups

,

Introduction :

A simple script to backup all filegroups within a database.

This will help in environments where filegroups and partioning are used to let a database span volumes for archive or performance reasons.

It will only work against databases using the FULL recovery mode.

Usage :

1) Change the @destinationpath parameter at line 12 to reflect your environment.

2) Execute the script against the database you want to backup.

Output :

The script displays and executes the appropriate backup commands to generate separate backup files for each filegroup.

Note :

I've commented out line 23, where (in my environment) I have my non-clustered indexes stored in a filegroup called NCIndexes,

In non live environments I script these rather than back them up to save space.

 

Let me know what you think,

r

 

Richard Doering

http://sqlsolace.blogspot.com

/*
Script  : Linked Server Connectivity Checker
Version : 1.0 (July 2010)
Author  : Richard Doering
Web     : http://sqlsolace.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

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating