December 15, 2014 at 3:02 am
My recommendation to my manager is to base recovery on .bak and .trn files. However the .mdf and .ldf files can be backed up without being detached and I am wondering about the value of these file backups. He has set up backups to external drive of the .mdf and .ldf but not of the .bak and .trn files that I have set up. His recovery strategy is based on complete backup of the machine and bare metal recovery. I am finding it hard to defend my preference for going about recovery with a new SQL Server installation, then restoring .bak files. Has anyone else tried recovery from .mdf and .ldf? Please note that all the database files (system and user) are on a single drive, I think this was set up to allow the machine to be clustered. We are intending to do a test rebuild of the machine from his backups but I appreciate opinions beforehand. Thanks.
December 15, 2014 at 3:56 am
It's a terrible idea 🙂
There's no guarantee that copying mdf & ldf results in a consistent backup - it almost certainly won't.
How often would the files be copied? There won't be any point-in-time recovery capabilities - only able to restore to the last time the files were copied, and that's if it works.
Of course, a failed test rebuild would be a good way to prove all this 😀
December 15, 2014 at 4:18 am
suedunham2 (12/15/2014)
My recommendation to my manager is to base recovery on .bak and .trn files. However the .mdf and .ldf files can be backed up without being detached and I am wondering about the value of these file backups. He has set up backups to external drive of the .mdf and .ldf but not of the .bak and .trn files that I have set up. His recovery strategy is based on complete backup of the machine and bare metal recovery. I am finding it hard to defend my preference for going about recovery with a new SQL Server installation, then restoring .bak files. Has anyone else tried recovery from .mdf and .ldf? Please note that all the database files (system and user) are on a single drive, I think this was set up to allow the machine to be clustered. We are intending to do a test rebuild of the machine from his backups but I appreciate opinions beforehand. Thanks.
Bad idea. Just copying the .ldf files won't truncate the log in full recovery model.
And most of the times the .mdf and .ldf files are locked by the SQL Server process, so you need to shut down SQL Server to copy them.
Bad bad idea 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 15, 2014 at 5:41 am
suedunham2 (12/15/2014)
My recommendation to my manager is to base recovery on .bak and .trn files. However the .mdf and .ldf files can be backed up without being detached and I am wondering about the value of these file backups. He has set up backups to external drive of the .mdf and .ldf but not of the .bak and .trn files that I have set up. His recovery strategy is based on complete backup of the machine and bare metal recovery. I am finding it hard to defend my preference for going about recovery with a new SQL Server installation, then restoring .bak files. Has anyone else tried recovery from .mdf and .ldf? Please note that all the database files (system and user) are on a single drive, I think this was set up to allow the machine to be clustered. We are intending to do a test rebuild of the machine from his backups but I appreciate opinions beforehand. Thanks.
By all means get the system state backups of the server, the database backups should be taken via sql server in order to ensure your backups are transactionally consistent. Raw file backups are not a recognised or even supported way of backing up the database.
I think you may be getting this confused with Oracle systems where the tablespaces are put into backup mode allowing a consistent backup of the raw data files, this is not the same though and shouldn't be viewed as such.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 15, 2014 at 5:50 am
The one thing that always validates whether or not any backup mechanism is any good is to run a restore.
So, use your bosses approach on 3-5 different databases, preferably the most important or the most used databases. If the restores all work and you have consistent, accessible databases, great. If not, then that backup approach doesn't work.
"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
December 15, 2014 at 7:16 am
Grant Fritchey (12/15/2014)
The one thing that always validates whether or not any backup mechanism is any good is to run a restore.So, use your bosses approach on 3-5 different databases, preferably the most important or the most used databases. If the restores all work and you have consistent, accessible databases, great. If not, then that backup approach doesn't work.
I'd worry that he'd be successful once, and then be stuck with a backup strategy that nobody else has much confidence in.
December 15, 2014 at 7:26 am
patrickmcginnis59 10839 (12/15/2014)
Grant Fritchey (12/15/2014)
The one thing that always validates whether or not any backup mechanism is any good is to run a restore.So, use your bosses approach on 3-5 different databases, preferably the most important or the most used databases. If the restores all work and you have consistent, accessible databases, great. If not, then that backup approach doesn't work.
I'd worry that he'd be successful once, and then be stuck with a backup strategy that nobody else has much confidence in.
Yeah, it could happen, but that's part of why I suggested multiple databases. Also, who knows, there are products out there that let you backup up the .MDF files directly that are transactional aware and will result in recoverable databases. They may be using one... probably not, but maybe.
"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
December 15, 2014 at 12:43 pm
Grant Fritchey (12/15/2014)
patrickmcginnis59 10839 (12/15/2014)
Grant Fritchey (12/15/2014)
The one thing that always validates whether or not any backup mechanism is any good is to run a restore.So, use your bosses approach on 3-5 different databases, preferably the most important or the most used databases. If the restores all work and you have consistent, accessible databases, great. If not, then that backup approach doesn't work.
I'd worry that he'd be successful once, and then be stuck with a backup strategy that nobody else has much confidence in.
Yeah, it could happen, but that's part of why I suggested multiple databases. Also, who knows, there are products out there that let you backup up the .MDF files directly that are transactional aware and will result in recoverable databases. They may be using one... probably not, but maybe.
If its the service I'm thinking about, the page below describes a version of it, and the OP should see related activity in his SQL logs. It would be worth a look!
December 15, 2014 at 8:31 pm
patrickmcginnis59 10839 (12/15/2014)
Grant Fritchey (12/15/2014)
patrickmcginnis59 10839 (12/15/2014)
Grant Fritchey (12/15/2014)
The one thing that always validates whether or not any backup mechanism is any good is to run a restore.So, use your bosses approach on 3-5 different databases, preferably the most important or the most used databases. If the restores all work and you have consistent, accessible databases, great. If not, then that backup approach doesn't work.
I'd worry that he'd be successful once, and then be stuck with a backup strategy that nobody else has much confidence in.
Yeah, it could happen, but that's part of why I suggested multiple databases. Also, who knows, there are products out there that let you backup up the .MDF files directly that are transactional aware and will result in recoverable databases. They may be using one... probably not, but maybe.
If its the service I'm thinking about, the page below describes a version of it, and the OP should see related activity in his SQL logs. It would be worth a look!
Yep. That's one way of doing it. I've seen it done that way. I haven't maintained it over time though. Not saying there's a problem, just that I haven't seen longer term situations that may arise, if any.
"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
December 16, 2014 at 1:51 am
The server team here think the same way as your boss. They moan all the time about the space needed for sql backups and the log backups in the day and say that their tape backups (once a day) of the mdf and ldf are good enough - even for business critical transactional systems such as the clocking in and out, time and attendance system which they now want to clone by virtualising the server then copying it!
Funny that when anything needs restoring it's my sql backups that get used! 😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply