SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup all Filegroups


Backup all Filegroups

Author
Message
r5d4
r5d4
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 846
Comments posted to this topic are about the item Backup all Filegroups
Peter.Frissen
Peter.Frissen
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1562 Visits: 324
Thanks. One comment:
while (@intCounter <= @intMaxId) should be
while (@intCounter < @intMaxId).
SQL_ME_RICH
SQL_ME_RICH
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2412 Visits: 1596
Will this also work for Filegroups that are specified Read-Only?
Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6598 Visits: 1632
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Mark D Powell
Mark D Powell
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2126 Visits: 466
I agree as otherwise I am getting the last file group twice.

Mark D Powell
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12192 Visits: 7444
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 Code
ALTER 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; They'll drag you down to their level and beat you with experience" ;-)
Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22364 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search