Backup all Filegroups

  • r5d4

    SSCrazy

    Points: 2499

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

  • Peter.Frissen

    SSCommitted

    Points: 1875

    Thanks. One comment:

    while (@intCounter <= @intMaxId) should be

    while (@intCounter < @intMaxId).

  • SQL_ME_RICH

    SSChampion

    Points: 11200

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

  • Robert Davis

    One Orange Chip

    Points: 28027

    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]

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4379

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

    Mark D Powell

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    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 @Server nvarchar(25) = @@SERVERNAME

    DECLARE @TimeStamp nvarchar(27)

    DECLARE @Random int

    DECLARE @UBound int = 9999999

    DECLARE @LBound int = 1000000

    DECLARE @intCounter int

    DECLARE @intMaxId int

    DECLARE @CurrentFile nvarchar(1000)

    DECLARE @ErrNo varchar(15)

    DECLARE @ErrMsg varchar(2000)

    DECLARE @Recipients varchar(200)

    DECLARE @Subject varchar(175)

    DECLARE @Body varchar(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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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