Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup all Filegroups Expand / Collapse
Author
Message
Posted Tuesday, July 27, 2010 9:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 8:26 AM
Points: 267, Visits: 674
Comments posted to this topic are about the item Backup all Filegroups
Post #959810
Posted Wednesday, July 28, 2010 12:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:54 AM
Points: 972, Visits: 202
Thanks. One comment:
while (@intCounter <= @intMaxId) should be
while (@intCounter < @intMaxId).
Post #959855
Posted Saturday, February 19, 2011 3:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:57 PM
Points: 461, Visits: 1,265
Will this also work for Filegroups that are specified Read-Only?
Post #1066816
Posted Tuesday, June 28, 2011 9:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1133382
Posted Wednesday, June 29, 2011 11:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:16 PM
Points: 1,343, Visits: 373
I agree as otherwise I am getting the last file group twice.

Mark D Powell
Post #1133964
Posted Thursday, February 09, 2012 10:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:46 PM
Points: 3,734, Visits: 7,081
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"
Post #1249833
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse