December 29, 2010 at 7:39 am
One of our clients have a Windows Server with MS SQL Server 2005 sp1 running.
Once a week, they shut their applications down and do a file back-up of everything on the server (on the data disks that is).
This also includes the .mdf and .ldf files of the databases.
I know it is certainly better to have a real back-up strategy creating .bak files (using full, differential or transaction log backups), but my question is:
are those .mdf and .ldf file backups useful?
Since SQL Server is shut down, the files aren't locked, so I guess they can be used.
If not, I better tell them just to skip the database files when they perform their back-up and to install a backup strategy using maintenance plans for their databases.
Any thoughts?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 29, 2010 at 8:16 am
Since SQL Server is turned off, yes, those backup files are useful. They can be used to reattach a database in the event of a disaster. Now, you won't have point in time recovery, but you will be able to retrieve up to the last backup.
It is a problematic approach though, as you've already outlined.
"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 29, 2010 at 8:18 am
Even my client has some servers in which they copy the .mdf & .ldf files in their "cold" backup strategy. If the application can afford the downtime, this strategy can be useful. If the original files get corrupt, they can be replaced with the "copy" files. Also the .mdf, .ldf files can be attached on some other instance as well.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
December 29, 2010 at 8:54 am
Hi All,
The similar case is with one of our clients which are using the Symantec Backup Exec 12.5. They have also purchased SQL Agent license from MS. Now they are performing Full backups daily in night without taking the SQL Server Offline. So ,
1. Is this type of backup methodolgy good enough ? i.e. can we consider these backups as consistent one.
Also, if they are taking full backups ONLY at night, I think Point in time restore for any database is impossible.?
Thanks,
With regards,
Ankur
December 29, 2010 at 9:00 am
ankur_libra (12/29/2010)
Hi All,The similar case is with one of our clients which are using the Symantec Backup Exec 12.5. They have also purchased SQL Agent license from MS. Now they are performing Full backups daily in night without taking the SQL Server Offline. So ,
1. Is this type of backup methodolgy good enough ? i.e. can we consider these backups as consistent one.
Also, if they are taking full backups ONLY at night, I think Point in time restore for any database is impossible.?
Thanks,
With regards,
Ankur
Hi Ankur,
Yes a full backup every night means that you should be able to recover up to the last full backup. And, yes, you're right, without log backups also being in place, there is no point in time recovery possible (assuming the database is also in Simple recovery mode).
BTW, for future reference, it's not considered polite to put your own questions about a different situation into someone else's thread. It can lead to confusion. It's better to start your own thread. Just so you know.
"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 29, 2010 at 9:05 am
Hi Grant,
Thanks for the response and Sorry for posting my question here.:crying:
With regards,
Ankur
December 29, 2010 at 9:17 am
ankur_libra (12/29/2010)
Hi Grant,Thanks for the response and Sorry for posting my question here.:crying:
With regards,
Ankur
No big deal. Like I said, for future reference.
It can just lead to confusion when people are talking about two different situations inside the same thread, that's all.
"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 29, 2010 at 11:30 am
Grant Fritchey (12/29/2010)
Hi Ankur,Yes a full backup every night means that you should be able to recover up to the last full backup. And, yes, you're right, without log backups also being in place, there is no point in time recovery possible (assuming the database is also in Simple recovery mode).
BTW, for future reference, it's not considered polite to put your own questions about a different situation into someone else's thread. It can lead to confusion. It's better to start your own thread. Just so you know.
Well, luckily I have a question on your answer, so the thread hijack is not a total waste 🙂
If the database has the full recovery model and full back-ups are taken every night, is point-in-time restore possible?
Or is it only possible with transaction log back-ups?
(and yes, you are right, thread hijacking can be confusing. I came back to this thread full with anticipation when I saw so many replies in my inbox :-))
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 29, 2010 at 11:38 am
OK, I just realized I asked a stupid question 🙂
If you have a full back-up at midnight you obviously can't restore to a point-in-time after midnight without transaction logs.
What I meant was:
can you partially restore a full back-up, or is it all or nothing?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 29, 2010 at 12:22 pm
Koen (da-zero) (12/29/2010)
Grant Fritchey (12/29/2010)
Hi Ankur,Yes a full backup every night means that you should be able to recover up to the last full backup. And, yes, you're right, without log backups also being in place, there is no point in time recovery possible (assuming the database is also in Simple recovery mode).
BTW, for future reference, it's not considered polite to put your own questions about a different situation into someone else's thread. It can lead to confusion. It's better to start your own thread. Just so you know.
Well, luckily I have a question on your answer, so the thread hijack is not a total waste 🙂
If the database has the full recovery model and full back-ups are taken every night, is point-in-time restore possible?
Or is it only possible with transaction log back-ups?
(and yes, you are right, thread hijacking can be confusing. I came back to this thread full with anticipation when I saw so many replies in my inbox :-))
If you can get a tail log backup, a backup of the log since the last log backup, yes, you can still do a point in time recovery.
But, if you have the database in full recovery, you MUST do a log backup at some point, even if it's just daily (although, depending on the system, you better have really big disks).
"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 29, 2010 at 12:33 pm
Grant Fritchey (12/29/2010)
If you can get a tail log backup, a backup of the log since the last log backup, yes, you can still do a point in time recovery.But, if you have the database in full recovery, you MUST do a log backup at some point, even if it's just daily (although, depending on the system, you better have really big disks).
Right, I forgot about the tail log backup 🙂
I did some more research and find out that full backups don't truncate the log (and I thought otherwise. Shame on me), so you are absolutely right.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply