Backup all Filegroups

  • Comments posted to this topic are about the item Backup all Filegroups

  • Thanks. One comment:

    while (@intCounter <= @intMaxId) should be

    while (@intCounter < @intMaxId).

  • Will this also work for Filegroups that are specified Read-Only?

  • If you have to do a full restore, how are you going to bring the index filegroup online if you don't have a backup?


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • I agree as otherwise I am getting the last file group twice.

    Mark D Powell

  • Not sure if anyone else experienced this issue, however, I'd like to share my experience:

    I modified the code a fair amount but kept the "core" portion of it in tact and everything appears to run just fine. Backups get created for each filegroup and so forth. However, when attempting to RESTORE one of the files to an actual database, I do not get an option to select a backup set to restore.

    Has anyone else experienced this? Or has anyone even tried to restore a database from this script?

    My CodeALTER PROCEDURE dbo.utl_BackupPrimaryDatabaseFileGroup (

    @DB nvarchar(12),

    @Destination nvarchar(150) = '\as2-backup1\sql_Backups\',

    @InclIndexes bit

    ) AS

    /*

    --------------------------------------------------------------------------------------------------------------

    Purpose :Creates FULL Backups of a specified DB, backing up only the PRIMARY filegroup

    Department :DB&R

    Created For :DBA

    Notes:@DB = Database to backup, @Destination = Location on the network

    Adapted/Improved from Author : Richard Doering

    Web - http://sqlsolace.blogspot.com

    @InclIndexes = Sets whether or not to include the NCIndex file group

    --------------------------------------------------------------------------------------------------------------

    Created On :2/9/2012

    Create By :MyDoggieJessie

    --------------------------------------------------------------------------------------------------------------

    Modified By:

    Modified On:

    Changes:

    #1

    --------------------------------------------------------------------------------------------------------------

    exec utl_BackupPrimaryDatabaseFileGroup 'SANDBOX', '\as2-backup1\sql_backups\', 0

    */

    DECLARE @Servernvarchar(25) = @@SERVERNAME

    DECLARE @TimeStampnvarchar(27)

    DECLARE @randomint

    DECLARE @UBoundint = 9999999

    DECLARE @LBoundint = 1000000

    DECLARE @intCounterint

    DECLARE @intMaxIdint

    DECLARE @CurrentFilenvarchar(1000)

    DECLARE @ErrNovarchar(15)

    DECLARE @ErrMsgvarchar(2000)

    DECLARE @Recipientsvarchar(200)

    DECLARE @Subjectvarchar(175)

    DECLARE @Bodyvarchar(MAX)

    SET @Destination = @Destination + @Server + '\\'

    SET @intCounter = 1

    SET @random = ROUND(((@UBound - @LBound -1) * RAND() + @LBound), 0)

    SET @TimeStamp = REPLACE(REPLACE(REPLACE(CONVERT(varchar(30),GETDATE(),20),'-',''),':',''),' ','_')

    + '_' +CAST(@Random AS nvarchar(7))

    /* Make sure the Destination directory is valid and exists */

    BEGIN TRY

    EXECUTE master.dbo.xp_create_subdir @Destination

    END TRY

    BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);

    DECLARE @ErrorSeverity INT;

    DECLARE @ErrorState INT;

    SELECT

    @ErrorMessage = ERROR_MESSAGE(),

    @ErrorSeverity = ERROR_SEVERITY(),

    @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

    END CATCH

    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#Files%')

    DROP TABLE #Files

    /* Create table to Store Potential Backups */

    CREATE TABLE #Files (

    id INT IDENTITY(1,1),

    command VARCHAR(1000)

    )

    IF @InclIndexes = 1

    BEGIN

    INSERT INTO #Files (command)

    SELECT 'BACKUP DATABASE [' + @DB +'] FILEGROUP = '''

    + fg.name + ''' TO DISK = N''' + @Destination

    + @DB + '_' + @TimeStamp + '_' + fg.name +'.bak'''

    + ' WITH NAME = ''' + @DB + '_' + @TimeStamp + '_' + fg.name +'.bak'''

    + ', NO_COMPRESSION, STATS = 10'

    FROM sys.master_files mf

    JOIN sys.filegroups fg ON

    mf.database_id = DB_ID(@DB)

    AND mf.data_space_id = fg.data_space_id

    JOIN sys.databases sd ON

    sd.database_id = DB_ID(@DB)

    WHERE mf.type <> 1

    AND sd.state_desc = 'ONLINE'

    AND sd.recovery_model_desc = 'FULL'

    AND fg.name = 'NCIndexes'

    ORDER BY fg.data_space_id

    END

    ELSE

    BEGIN

    INSERT INTO #Files (command)

    SELECT 'BACKUP DATABASE [' + @DB +'] FILEGROUP = '''

    + fg.name + ''' TO DISK = N''' + @Destination

    + @DB + '_' + @TimeStamp + '_' + fg.name +'.bak'''

    + ' WITH NOFORMAT, NOINIT, NAME = ''' + @DB + '_' + @TimeStamp + '_' + fg.name +'.bak'''

    + ', SKIP, REWIND, NOUNLOAD, NO_COMPRESSION, STATS = 10'

    FROM sys.master_files mf

    JOIN sys.filegroups fg ON

    mf.database_id = DB_ID(@DB)

    AND mf.data_space_id = fg.data_space_id

    JOIN sys.databases sd ON

    sd.database_id = DB_ID(@DB)

    WHERE mf.type <> 1

    AND sd.state_desc = 'ONLINE'

    AND sd.recovery_model_desc = 'FULL'

    AND fg.name <> 'NCIndexes'

    ORDER BY fg.data_space_id

    END

    SELECT @intMaxId = MAX(ID) FROM #Files

    WHILE (@intCounter <= @intMaxId)

    BEGIN

    SELECT @CurrentFile = command FROM #Files WHERE id = @intCounter

    /* Perform the backup */

    BEGIN TRY

    PRINT (@CurrentFile)

    SET @intCounter = @intCounter + 1

    EXEC sp_executeSQL @CurrentFile

    END TRY

    BEGIN CATCH

    SELECT @ErrNo = ERROR_NUMBER(), @ErrMsg = ERROR_MESSAGE()

    SET @ErrMsg = 'ERROR :: ' + @ErrNo + CHAR(13)

    SET @Subject = 'ERROR :: Cannot backup the ' + @DB + ' database on ' + UPPER(@Server) + '!'

    SET @Body = 'There was an error backing up the ' + @DB + ' database files:' + CHAR(13)

    + '-----------------------------------------------------------------------------------' + CHAR(13)

    + @CurrentFile + CHAR(13)

    + '-----------------------------------------------------------------------------------' + CHAR(13)

    + @ErrMsg

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = @Recipients,

    @copy_recipients = 'woof@ssc.com',

    @subject = @Subject,

    @body = @Body,

    @profile_name = 'DoggieMail',

    @body_format = 'TEXT',

    @importance = 'High'

    END CATCH

    END

    /*

    exec utl_BackupPrimaryDatabaseFileGroup 'SANDBOX', '\as2-backup1\sql_backups\'

    */

    GO

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Thanks for the script.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply