Link to home
Create AccountLog in
Avatar of indikad
indikad

asked on

SQL Server Backup strategies - SQL backup vs file backup

what are your ideas on SQL server backup strategies

While you can do backup using the Backup Job within SQL Server  ( which I think is the best way )

there are some system admins that take just file backups( as in taking a native windows file backup  - and NOT SQL Server file backup option) . This simply backups the .mdf file and .ldf file as the transactions go on the database.

In my view the file backups as above can be an issue - if the .mdf file had some uncommitted transactions ,  and when you try to re-attach a .mdf file in a disaster recovery situation you are in trouble. ( am I right/wrong ?)

If there are other issues , can someone elaborate on the pitfalls of file backups so I can technically explain this to someone else ?

Also I need to know the advantages of SQL backups.
Avatar of MrAli
MrAli
Flag of United States of America image

OMG what the hell is wrong with these admins?!  If I were their DBA I would ask management to put their heads on spikes!!  People who have no idea how a database works (such as these alleged 'admins') should be publicly humiliated for playing games with the company data.  

NO.  Backing up the MDF/LDF files is ABSOLUTELY NOT OK in ANYWAY what so ever!  First off, those files are VERY sensitive!!  I have seen them get corrupted by just making a copy of them moving from fast disks to slow disks.  I've seen them not work from emailing them back and fourth.  Nevermind there are potential other issues, *and* MS is removing the capability to just attach a DB from MDF files in future versions.

There are some API's if I recall right for VSS, but I haven't used them and I don't trust them, however they *might* be good.

I would put the DB under a fair amount of load, have open transactions, take a 'backup', then try to test restore it.  It might work, and it might not.  Do it 100 times and I can almost ensure you will have failures, maybe even on the first try.

Ask these 'admins' if they EVER did any test restores.  Did it work?
ASKER CERTIFIED SOLUTION
Avatar of indikad
indikad

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Good luck indikad, I'm glad you took the extra step to try to validate your thoughts.  I'm sorry you have to deal with this.  I suspect you'll want to test their strategy, there's a slim chance they are using the VSS service to do the backups which MIGHT make it OK but then they should test restore the backups.  I test restore all of my critical backups nightly via auto restore jobs.
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
Avatar of Racim BOUDJAKDJI
Racim BOUDJAKDJI
Flag of Algeria image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Very well put Racimo.  I end up calling them lazy sysadmins that just want to collect a paycheck and really couldn't give a damn about the quality of their data or job.  

Seeing how almost every company that suffers catastrophic data loss (over 80%) go bankrupt within 5 years, they also probably don't care much about the company.  Why else would they do such things?  If I were the manager they would promptly be fired.

Perhaps you should show their boss these statistics and ask them, "Do you trust your entire company in the hands of this guy?"
Avatar of indikad
indikad

ASKER

Thank you for all of you.