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.
Go to Top of Page

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
Go to Top of Page

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 :(

Go to Top of Page

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2013-10-08 : 11:57:11
Does anyone else have an answer?
Go to Top of Page

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


Go to Top of Page
   

- Advertisement -