InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 12:31:09 GMT20http://www.sqlservercentral.com/Forums/Topic959810-1631-1.aspxNot 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 [b]RESTORE[/b] 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[code="sql"]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) = @@SERVERNAMEDECLARE @TimeStamp nvarchar(27)DECLARE @Random intDECLARE @UBound int = 9999999DECLARE @LBound int = 1000000DECLARE @intCounter intDECLARE @intMaxId intDECLARE @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 = 1SET @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 @DestinationEND TRYBEGIN 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 CATCHIF 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 &lt;&gt; 1 AND sd.state_desc = 'ONLINE' AND sd.recovery_model_desc = 'FULL' AND fg.name = 'NCIndexes' ORDER BY fg.data_space_id ENDELSE 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 &lt;&gt; 1 AND sd.state_desc = 'ONLINE' AND sd.recovery_model_desc = 'FULL' AND fg.name &lt;&gt; 'NCIndexes' ORDER BY fg.data_space_id END SELECT @intMaxId = MAX(ID) FROM #Files WHILE (@intCounter &lt;= @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 CATCHEND/* exec utl_BackupPrimaryDatabaseFileGroup 'SANDBOX', '\as2-backup1\sql_backups\'*/GO[/code]Thu, 09 Feb 2012 10:19:12 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic959810-1631-1.aspxI agree as otherwise I am getting the last file group twice.Mark D PowellWed, 29 Jun 2011 11:56:21 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic959810-1631-1.aspxIf you have to do a full restore, how are you going to bring the index filegroup online if you don't have a backup?Tue, 28 Jun 2011 21:33:56 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic959810-1631-1.aspxWill this also work for Filegroups that are specified Read-Only?Sat, 19 Feb 2011 15:04:07 GMTSQL_ME_RICHhttp://www.sqlservercentral.com/Forums/Topic959810-1631-1.aspxThanks. One comment:while (@intCounter &lt;= @intMaxId) should be while (@intCounter &lt; @intMaxId).Wed, 28 Jul 2010 00:49:02 GMTPeter.Frissenhttp://www.sqlservercentral.com/Forums/Topic959810-1631-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/T-SQL/70769/">Backup all Filegroups</A>[/B]Tue, 27 Jul 2010 21:02:37 GMTr5d4