July 27, 2010 at 9:02 pm
Comments posted to this topic are about the item Backup all Filegroups
July 28, 2010 at 12:49 am
Thanks. One comment:
while (@intCounter <= @intMaxId) should be
while (@intCounter < @intMaxId).
February 19, 2011 at 3:04 pm
Will this also work for Filegroups that are specified Read-Only?
June 28, 2011 at 9:33 pm
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?
June 29, 2011 at 11:56 am
I agree as otherwise I am getting the last file group twice.
Mark D Powell
February 9, 2012 at 10:19 am
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
May 18, 2016 at 1:14 pm
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