I have a SQL 2008 server(10.0.5500.0), SSMS on my local PC is SQL 2008 (10.0.5500.0) and when I look at any Database's database properties on this server, the Last Database Backup value shows NONE for database and log backups, including System databases. In the SQL Logs, I do see entries for Backup and Log Backups that are completed. Currently, our Network Services area handles daily SQL backups using Backup Exec software, but we have been using it for a few years now and at one time I was able to see dates in these fields. I even found a script on a website that said it would show me when my last backup was taken, but it ALSO returned no data. It joined "sys.databases" to "msdb.dbo.backupset".
Has something changed? Should I be concerned?
100% agree! I wonder if a restore has ever been tested.
You may want to talk to your network services team/symantec people to make sure you're correctly backing up the databases. I do believe though that if backup exec takes backups through VSS it won't show up in the msdb tables... I believe on native backup and restore commands populate those table but I could be completely wrong, hence speaking to your team and symantec... or consulting their documentation.
In August, I had Network Services restore a full database backup to my server, but no transaction logs and the restore worked. I also have one Accounting database that I allow to be backed up via a SQL job using SQL Backup statements to a disk file. I then restore that database after we do some pre-closing calculations. I just did that backup and restore the first week of October and was able to get that to work, but when I go to the SQL SSMS Restore Database window for that database, I cannot see any backup sets in the list including the one I did via SQL statements a few weeks ago.
As far as populating the backup tables, another oddity is that on 2 other servers I can see Last Database backup dates for "some" databases, but none of these(with Full recovery) show a date for Last Database Log Backup. All these servers should be using the same Backup Exec software, so what is different?
I have notified my Network Services dept, but just wanted to know if anyone else had seen these problems. I have never liked that SQL backups were done outside SQL Server, but lost that battle to our Network Services dept a few years ago. I may have leverage to win it back now.
ALSO....since I posted the original question, I did some research on Backup Exec and found that a new release called "2012" has had SEVERAL problems. I'm really hoping we are not using that version, but that could be the culprit. In case anyone else finds this link here is a disturbing bit of information I found on Backup Exec's newest version(s):
I cut and paste this directly from Symantec's Enterprise support page:
Solution1. Make sure no SQL backups run outside Backup Exec (including SQL in-built utility for Backups and Restores) as that would interrupt the continuity of SQL backups within Backup Exec schedule causing the job to fail.
- Proposed as answer by vr.babu Tuesday, October 30, 2012 8:59 AM
If you have any type of maintenance scripts or plans running, they may be deleting older backup and restore data.
Thanks for the reminder. I found a couple of maintenance plans on this server, but only 1 was running and it was created by Network Services to backup the system databases. Why, I'm not sure, since they are also backing these up with Backup Exec. BUT, while I did not find any plans deleting history data on the server, I'm wondering if the Backup Exec software has an option for that and the Network group has somehow set this incorrectly. Looks like I have a lot of work to do to fix our backup process :(
The query you referenced should work and show you when the last backups were taken, what they were, and the size of the backup.
select description, database_name,backup_finish_date from dbo.backupset where database_name = '<database name>' order by backup_finish_date DESC
As you can see, we will display Snapshot Full backups, Streaming (traditional VDI) full backups, and the Streaming (traditional) log backups.
You stated that the backups are being indicated as completed within the SQL logs but they don’t show on the properties of the SQL Server Management Studio? Is that correct? Also that the query you run, as listed above, returns no results, correct?
You should check the Backup Exec Log files, the backup sets tab, and the event viewer to ensure the backups ran.I don’t think she It doesn't sound like you are getting backups of the databases.
The comment you cut and paste from the Symantec Enterprise support page, do you have a direct link to that? I'd like to follow it up and make some changes to it.
What we should be stating regarding this, as we do in the job failures, is that in Backup Exec, log backups are not compatible if the Database Full backup was not done by Backup Exec. That’s a much different story than what’s posted above, and I'd like to get it corrected ASAP.
Database and Log backups are showing in SQL Server Logs. When I ran a select on dbo.backupset yesterday, there were no records. I just ran it again and there are records, but all are dated between 1:30am and 4:30am this morning which is when the SQL Logs show these backups running. So I'm guessing there must be a job running sometime during the day that is clearing out the dbo.backupset history records, which would explain why the file was empty yesterday afternoon. (OR, the BE backup did not work, but history records were cleared anyway - they actually fail fairly often) I cannot check Backup Exec logs or run the software, that's exactly the point - I do not have access to that software as it is considered "hardware" related and not in my domain. I've attached the link for my quote below, but even rewording it will not change the fact that once a non-Backup Exec log or diff backup is done it will break the database/log restore chain. It has been maybe 2 years, but in the past we were able to restore a BE database, then apply log backups even if the logs were done outside of BE. It was not easy, but we did it. It sounds like that will no longer work.