July 11, 2013 at 9:12 am
I was asking the network/system guy whether a third-party app's databases were being backed up. He did some checking and said "Yes" but I suspect he meant that it's the .mdf and .ldf files that are being backed up (copied). A quick search did not find any .bak files for those databases.
I think I know (at least part of) the answer, but wanted to get some thoughts from people on this forum.
1) is it OK to rely on a copy of the .mdf and .ldf files as a backup?
2) if the current version becomes corrupt can you go back to a previous copy and restore that?
July 11, 2013 at 9:35 am
While a database is active you cannot backup the mdf, ndf, ldf files (remember these don't have to be the file extension, they are just microsoft's naming convention).
Some 3rd Party softwares have a backup agent that can back these files up, but as they can change during the period it takes to write the backup to media they can be inconsistent.
The best think is to backup your DB backup files to a media device (tape etc). Also remember backup files do not have to have a .bak extension so you may be searching for the wrong files. The backup history tables in msdb will tell you if the database has been backed up at some point which should help answer your question.
MCITP SQL 2005, MCSA SQL 2012
July 11, 2013 at 10:09 am
timwell (7/11/2013)
1) is it OK to rely on a copy of the .mdf and .ldf files as a backup?
No.
2) if the current version becomes corrupt can you go back to a previous copy and restore that?
Maybe. How far back you have to go to find a copy that works, that attaches correctly is another matter.
What's your RPO for this DB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2013 at 10:22 am
Thanks for your reply.
It may not be as bad as I thought. There are backups in the history tables. (backupfile etc. in msdb)
As for where the backups are going, there are names like this in the physical_device_name field of the backupmediafamily table
device554FC021
device554FC034
Does anyone know what these refer to? Some rows have file paths in this field.
July 11, 2013 at 10:29 am
Working with CommVault backups here, names like that seem to be "virtual devices" created just for the backup to go to. It's not a "file" or "folder" as such, but something that exists within the backup application.
The backups with file paths, those were most likely backups created by someone through SSMS / SQLCMD backups.
Jason
July 11, 2013 at 10:49 am
If you are the DBA, you should ensure that you are running regular backups. Don't rely on the .mdf and .ldf files being copied somewhere else. Take a full backup periodically and transaction log backups more frequently. How often depends on your business, SLAs, how much data you can afford to lose, etc. We do full backup daily and transaction logs every 30 minutes.
Relying on .mdf and .ldf files being copies somewhere leads to a host of problems, not the least of which is runaway transaction logs. If you don't ever back them up, they'll just keep growing. Backing them up marks the backed entries as available for use. You also lose all data between the time when the files were backed up and the point of failure. Most organizations are not willing to live with a full day's loss of data.
In the end, it is up to you (as the DBA) to make sure you can recover when needed. When you get a strategy in place, test your strategy by doing a practice run. See how long it takes you and how easy it is. I've heard it said by others that you don't need a good backup plan, but a good recovery plan. This means to put a system in place and test it to make sure it works. You don't want your first recovery attempt to be the one where a production system is down.
July 12, 2013 at 8:03 am
Thanks for the information.
I am sort of an accidental DBA; most of my experience is as a developer. I am learning a lot from the articles and forums on this site.
We have a backup service so that may be where the backup to virtual devices is going.
Although I am not specifically responsible for the other databases, I think I will start a conversation about disaster recovery.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply