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


Backup all Filegroups


Backup all Filegroups

Author
Message
r5d4
r5d4
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

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

Group: General Forum Members
Points: 1530 Visits: 318
Thanks. One comment:
while (@intCounter <= @intMaxId) should be
while (@intCounter < @intMaxId).
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)SSC Eights! (932 reputation)

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

Group: General Forum Members
Points: 2836 Visits: 1623
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

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

Mark D Powell
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6728 Visits: 7394
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14046 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