Full backup monthly job is failing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Full backup monthly job is failing

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2013-10-01 : 11:34:49

Hi,

I have a monthly job running on my SQL server.

- It backups all the online databases to a location "\\Backupserver\e$\MonthEndBackups\".

- Job runs the USP below like this: exec [usp_dba_BackupDatabases] 3
(http://screencast.com/t/l7IS5TZK)

- It runs on the last day of the month.

Our databases were scattered around multiple servers and this job runs on those servers and this used to work.

We consolidated all the database on the same server (same SQL instance) and this job does not seem to backup all the databases. I am not sure why? My job has notification(email) on failure and also writes to a log file.

http://screencast.com/t/8ioTZdqEMg9x
http://screencast.com/t/VI3d4GLBTGoX

But nothing fails, so nothing is on the logs and no email notifications show up.

I know it did not work as I don't see the full backups in the folder.

here is the schedule setup:

http://screencast.com/t/waeGwLSa

What could be going wrong? I don't see any pattern in the databases that are not backing up. There are larger databases that are getting backed up.

Can anyone think of why this could be happening? is there any way to trouble shoot this?


USE [DBA]
GO

/****** Object:  StoredProcedure [dbo].[usp_dba_BackupDatabases]    Script Date: 10/01/2013 11:10:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_dba_BackupDatabases] 
	@pBackupType		SMALLINT
	,@pDatabaseName		sysname	= NULL
	
AS



SET NOCOUNT ON


DECLARE @vDatabase sysname, @sql VARCHAR(MAX), @vBackupFileFullPath VARCHAR(MAX)

DECLARE c CURSOR FOR 
SELECT  name FROM sys.sysdatabases
WHERE name NOT IN('tempdb', 'model')
	AND DATABASEPROPERTYEX (name,'STATUS') IN( 'ONLINE')
	AND (name  = @pDatabaseName OR @pDatabaseName IS NULL)

OPEN c
FETCH NEXT FROM c INTO @vDatabase
WHILE @@FETCH_STATUS = 0 
BEGIN

SELECT CONVERT(VARCHAR,GETDATE(),121)
	PRINT '|-->' + CONVERT(VARCHAR,GETDATE(),121) +' Backup Start for database ' + @vDatabase 

	IF @pBackupType = 1
		BEGIN
			SET @vBackupFileFullPath = '\\Backupserver\d$\' + @@SERVERNAME +'\' + @vDatabase +'_DB_'
				+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),GETDATE(),112),'-',''),' ',''),':','') +'.bak'
			SET @sql  = 'BACKUP DATABASE ' + @vDatabase + ' TO  DISK = N' + CHAR(39) + @vBackupFileFullPath +  char(39) 
			+ ' WITH FORMAT, INIT,  NAME = N' + CHAR(39) + @vDatabase + ' -Full Database Backup' + CHAR(39) + ', SKIP, NOREWIND, NOUNLOAD,  STATS = 5, CHECKSUM'
		END
		

	IF @pBackupType = 2
		BEGIN
			SET @vBackupFileFullPath = '\\Backupserver\d$\' + @@SERVERNAME + '\Differential\' + @vDatabase +'_Diff_'
				 +REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),GETDATE(),121),'-',''),' ',''),':','') +'.bak'		
			SET @sql  = 'BACKUP DATABASE ' + @vDatabase + ' TO  DISK = N' + CHAR(39) +  @vBackupFileFullPath + char(39)
				 + ' WITH DIFFERENTIAL , FORMAT, INIT, NAME = N' + CHAR(39) + @vDatabase 
								+ ' -Differential Database Backup' + CHAR(39) + ', SKIP, NOREWIND, NOUNLOAD,  STATS = 5, CHECKSUM'				
		END
		
	IF @pBackupType = 3
		BEGIN
			SET @vBackupFileFullPath = '\\Backupserver\e$\MonthEndBackups\' + @@SERVERNAME +'_'+ @vDatabase +'_db.bak'
			SET @sql  = 'BACKUP DATABASE ' + @vDatabase + ' TO  DISK = N' + CHAR(39) + @vBackupFileFullPath  + char(39) 
				+ ' WITH COPY_ONLY, FORMAT, INIT,  NAME = N' + CHAR(39) + @vDatabase + ' -Full Month End Database Backup' + CHAR(39) + ', SKIP, NOREWIND, NOUNLOAD,  STATS = 5, CHECKSUM'			
		END
		
	PRINT '	|'+ @sql  
	EXEC (@sql )


	--VERIFY BACKUP
	
	IF @pBackupType = 1
		SET @sql  = 'RESTORE VERIFYONLY FROM DISK = N' + CHAR(39) + @vBackupFileFullPath +  char(39) 
					+ ' WITH  FILE = 1,  NOUNLOAD,  NOREWIND'

	IF @pBackupType = 2
		SET @sql  = 'RESTORE VERIFYONLY FROM DISK = N' + CHAR(39) + @vBackupFileFullPath + char(39)
				 + ' WITH  FILE = 1,  NOUNLOAD,  NOREWIND'

	IF @pBackupType = 3
		SET @sql  = 'RESTORE VERIFYONLY FROM DISK = N' + CHAR(39) + @vBackupFileFullPath + char(39) 
					+ ' WITH  FILE = 1,  NOUNLOAD,  NOREWIND'
	
	--PRINT '	|'+ @sql  
	--EXEC (@sql )

	
	
	PRINT '|-->' + CONVERT(VARCHAR,GETDATE(),121) +' Backup Complete for database ' + @vDatabase 
	PRINT '' 

	FETCH NEXT FROM c INTO @vDatabase
END

CLOSE c
DEALLOCATE c



GO

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2013-10-01 : 13:07:24

I did this on local machine with empty databases in same names and it worked fine.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-10-01 : 18:41:58

1) What type of backups are you attempting? Full? Diff? Log?
2) Does the SQL Service account on the failing server have access to the destination? Is it different from the SQL Service account on your local machine?
3) Have you tried logging informational messages within your job?

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2013-10-02 : 09:13:14

Thanks for the reply.

1. Full backups.
2. I am not 100% sure. But it should be. I was thinking of that too. But all the databases are on the same server and same instance and teh destination for all of them are same. So, if it does not have access all database backups should fail. There are only selected ones that is failing.
3. Do you mean like this ? http://screencast.com/t/8ioTZdqEMg9x
Yes. And this has detailed log off all the databases backup process. Since the backups don't literally fail, it does not have entries for those databases missing. So, no error logs any where but few database backups don't exist. WEIRDDDD :(

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2013-10-08 : 11:57:11

Does anyone else have an answer?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-10-11 : 13:31:38

There are some issues with your script - and that could be causing the issues.

If any of the database names have any type of special characters, or spaces - it will cause that database backup to fail. Since you are not checking any error status - most likely it fails and just goes to the next one in the list.

If the database name has a character that is not allowed in a directory/file name it will also cause the command to fail.

I would recommend reviewing those databases that are failing and make sure they do not have an extra space in the name (usually at the end). I would also recommend that you look up 'quotename' in BOL and use that to quote the database name and anything else to be quoted.

Example:

QUOTENAME(@vDatabase) -- will place brackets around the name (e.g. [db1]
QUOTENAME(@vBackupFileFullPath, char(39)) -- puts single-quotes around the full path name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources