January 3, 2017 at 7:19 am
My job that backs up several Databases fails. I can not determine which one is failing. I did not create the maintenance plan and I m not familiar with all of the databases.
I check all of the folders and I am unable to identify what backup is failing.
I get the following error:
Source: Back Up Database (Full) Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete
End Progress Progress: 2017-01-01 14:23:49.82 Source: Back Up Database (Full) Execu... The package execution fa... The step failed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 3, 2017 at 8:07 am
January 3, 2017 at 8:31 am
Thomas' stuff looks excellent. I'd follow that method. If not, just querying the backupset system database can at least tell you what has been successfully backed up. You can then use a process of elimination to determine what has not been backed up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2017 at 8:33 am
Welsh Corgi (1/3/2017)
My job that backs up several Databases fails. I can not determine which one is failing. I did not create the maintenance plan and I m not familiar with all of the databases.I check all of the folders and I am unable to identify what backup is failing.
I get the following error:
Source: Back Up Database (Full) Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete
End Progress Progress: 2017-01-01 14:23:49.82 Source: Back Up Database (Full) Execu... The package execution fa... The step failed.
Nice Article but I am trying to identify which backup failed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 3, 2017 at 8:49 am
Grant Fritchey (1/3/2017)
Thomas' stuff looks excellent. I'd follow that method. If not, just querying the backupset system database can at least tell you what has been successfully backed up. You can then use a process of elimination to determine what has not been backed up.
I run the query and I show that all databases have been backed up but the job fails when performing a backup and I can not identify the issue.
Do you have an example to query the backup set?
Thank to all.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 3, 2017 at 8:55 am
Welsh Corgi (1/3/2017)
Do you have an example to query the backup set?
In that blog post that you read earlier.
January 3, 2017 at 9:19 am
OK, here's a simpler query that may be easier to understand?
-- most recent backups per database
SELECT LEFT(d.name,20) AS DBname, LEFT(d.state_desc,10) AS StateDesc, LEFT(d.recovery_model_desc,10) AS RecoveryModel,
CONVERT(VARCHAR(20), fb.LastFullBackup, 120) AS LastFullBackup,
CONVERT(VARCHAR(20), db.LastDiffBackup, 120) AS LastDiffBackup,
CONVERT(VARCHAR(20), lb.LastLogBackup, 120) AS LastLogBackup,
CONVERT(VARCHAR(20), ob.LastOtherBackup, 120) AS LastOtherBackup
FROM sys.databases d
LEFT OUTER JOIN
(SELECT database_name, MAX(backup_finish_date) AS LastFullBackup
FROM msdb.dbo.backupset WHERE type = 'D'
GROUP BY database_name) fb ON d.name = fb.database_name
LEFT OUTER JOIN
(SELECT database_name, MAX(backup_finish_date) AS LastDiffBackup
FROM msdb.dbo.backupset WHERE type = 'I'
GROUP BY database_name) db ON d.name = db.database_name
LEFT OUTER JOIN
(SELECT database_name, MAX(backup_finish_date) AS LastLogBackup
FROM msdb.dbo.backupset WHERE type = 'L'
GROUP BY database_name) lb ON d.name = lb.database_name
LEFT OUTER JOIN
(SELECT database_name, MAX(backup_finish_date) AS LastOtherBackup
FROM msdb.dbo.backupset WHERE type NOT IN('D','I','L')
GROUP BY database_name) ob ON d.name = ob.database_name
ORDER BY CASE WHEN d.database_id <= 4 THEN 0 ELSE 1 END, d.name
which ever one has the oldest most-recent backup date would be the likely one that failed.
January 3, 2017 at 9:24 am
Beatrix Kiddo (1/3/2017)
Welsh Corgi (1/3/2017)
Do you have an example to query the backup set?
In that blog post that you read earlier.
Nice but it does not show which backup failed.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 3, 2017 at 9:29 am
No, as the other posts state, you do have to do some of the legwork yourself, by process of elimination.
January 3, 2017 at 9:55 am
Sorry, I was away from email. However, the other answers are what you need to do. Get a list of successful backups (which was a part of Thomas' article) and then compare that to your databases to find the difference. The other query is good too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2017 at 12:23 pm
Grant Fritchey (1/3/2017)
Sorry, I was away from email. However, the other answers are what you need to do. Get a list of successful backups (which was a part of Thomas' article) and then compare that to your databases to find the difference. The other query is good too.
I should have mentioned that I already did that and all the databases are listed as being backed up.
I do not know why the job fails?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 3, 2017 at 12:54 pm
Welsh Corgi (1/3/2017)
I should have mentioned that I already did that and all the databases are listed as being backed up.I do not know why the job fails?
OK, so the problem isn't identifying the database, it's the error gets cut off in SQL Server Agent history? Maybe try a technique like output files:
https://www.mssqltips.com/sqlservertip/1411/verbose-sql-server-agent-logging/
January 4, 2017 at 5:53 am
I agree with Chris. If you can verify that all your databases are being backed up, then the issue is somehow within the job itself. It's not exiting cleanly. You'll need to look at the code there to identify the issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply