SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Full backup monthly job is failing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shiyam198
Yak Posting Veteran

94 Posts

Posted - 10/01/2013 :  11:34:49  Show Profile  Reply with Quote
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 - 10/01/2013 :  13:07:24  Show Profile  Reply with Quote
I did this on local machine with empty databases in same names and it worked fine.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 10/01/2013 :  18:41:58  Show Profile  Reply with Quote
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
Go to Top of Page

shiyam198
Yak Posting Veteran

94 Posts

Posted - 10/02/2013 :  09:13:14  Show Profile  Reply with Quote
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 :(

Go to Top of Page

shiyam198
Yak Posting Veteran

94 Posts

Posted - 10/08/2013 :  11:57:11  Show Profile  Reply with Quote
Does anyone else have an answer?
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
798 Posts

Posted - 10/11/2013 :  13:31:38  Show Profile  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000