February 9, 2012 at 1:37 pm
DISCLAIMER :: I am not an expert scripting out DB backups by any means, but the code I've come up with (in part derived from an article here on SSC) "appears" to work, meaning I see files being backed up to a specified network location, however, I cannot seem to get them to successfully restore to a new database (therefore, as far as I'm concerned it's useless :crazy:)
Why I am attempting this: Our nightly production DB backups have grown quite large (about 300GB after being compressed by HyperBac) and it's taking approcximately 3-4 hours to complete nightly. I am hoping to segregate the PRIMARY filegroup and an alternate INDEX filegroup into seperate backups. In testing, this significantly speeds up the data backup time and size on disk.
When I run the script below, I get the following files outputed to the backup location:
SANDBOX_20120209_115559_4464406_PRIMARY.bak
SANDBOX_20120209_115559_4464406_Test1.bak
SANDBOX_20120209_115559_4464406_Test2.bak
SANDBOX_20120209_115602_7632589_NCIndexes.bak
I am using the GUI in SSMS to restore the files but am not able to get it working.
When I try:
1. Restore Files and Filegroups
2. Eg: [New database name] = ABC
3. Choosing "From Device", then specifiying the PRIMARY BAK file
4. Entering SANDBOX for the "File Logical Name"
- Successfully runs, restores the backup
But I still need the other 3 files, don't I?
When I try to include the other files by:
1. Restore Files and Filegroups
2. Database: ABC
3. Choosing "From Device", then specifiying the Test1.BAK file
4. Entering SANDBOX for the "File Logical Name"
I receive this error:
Restore failed for Server 'BM015Q1-6410'. (Microsoft.SqlServer.SmoExtended)
File 'SANDBOX' was not backed up in file 1 on device '\as2-backup1\sql_backups\BM015Q1-6410\SANDBOX_20120209_115559_4464406_Test1.bak'. The file cannot be restored from this backup set.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3144)
If I change the "File Logical Name" to "ABC" I get a slightly different error:
Restore failed for Server 'BM015Q1-6410'. (Microsoft.SqlServer.SmoExtended)
Logical file 'ABC' is not part of database 'ABC'. Use RESTORE FILELISTONLY to list the logical file names.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3234)
If I repeat steps 1-3 above then attempt to add in all the BAK files (so I have all 4 total in the backup sets list) I get a more bizarre error:
An exception occurred while executing a Transact-SQL statement or batch.
I get the same error above if I even attempt to click on the "Options" tab...
What am I doing wrong?
If I can get this working properly I will most likely opt to script out the non-clustered indexes nightly as well, instead of backing up the INDEX filegroup...but need to make certain that I can successfully restore additional files/filegroups first.
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 @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 */
EXECUTE master.dbo.xp_create_subdir @Destination
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 NOFORMAT, NOINIT, NAME = ''' + @DB + '_' + @TimeStamp + '_' + fg.name +'.bak'''
+ ', SKIP, NOREWIND, 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
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 = 'mydoggiejessie@doggiemail.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
February 10, 2012 at 1:33 pm
Anyone?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 10, 2012 at 9:50 pm
You are not selecting the right backup..
February 13, 2012 at 8:36 am
I think I've looked at the code for too long and can't see the obvious, can you please tell me where I am missing that?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 14, 2012 at 9:49 am
I suggest you look at Ola Hallengren's databae maintenance solution. http://ola.hallengren.com/ It has won a number of awards, and is likely to be a lot more reliable than something you build yourself.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 15, 2012 at 9:37 am
I am quite familiar with that solution, but don't believe it allows for what I am trying to do specifically
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply