Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

There is a lot of software out there that backs up SQL Server directly out of the database, rather than backing up a file which was pulled out of SQL Server. I have always been fearful of using it, in terms of its impact on the production system. Does anyone have any experience with this? Is it reliable? Does it matter what product you are using, or does it interact with known SQL Server APIs, so it doesn't really matter?

share|improve this question
add comment

5 Answers

up vote 4 down vote accepted

The native backups are just as reliable as the third party tools. The third party tools actually use the native backup to backup the database. They simply setup a virtual tape drive, and to the backup to that device. That device is actually there dlls which do extra compression, etc on the backup.

Backing up the database files via net backup without stopping the SQL Server database is NOT supported and shouldn't be used.

share|improve this answer
    
what about backups that use VSS writer? –  Nick Kavadias Aug 27 '09 at 0:28
    
Those that are certified using VSS writer should be fine EMC's Replication Manager product for example uses VSS to handle the backups. However not every backup solution that uses VSS has the Microsoft stamp of approval on it. If they give you a crash restartable backup like EMC does you should be fine. However if they give you a crash recoverable backup it isn't quite as good. EMC even says that the backup that RM makes has a 99.9% chance of restoring correctly. It rarely fails to restore, but it has happened. –  mrdenny Aug 27 '09 at 21:31
add comment

I have experience (beyond SQL Server's native backup functionality) with Redgate's SQL Backup and Quest's LiteSpeed for SQL Server, both of which are rock solid and for me work faster than native SQL backup. They both install extended stored procedures into your SQL instance. In my experience it's far more reliable to use one of these products, or even SQL's native backup functionality, than to back up from the filesystem level. You also have many more options available to you by using SQL's native backup or a third party backup package.

share|improve this answer
add comment

SQL Server backups are enterprise grade. You can create a batch script that backs up your database and then run it in the scheduler.

We did not purchase anything other than SQL Server 2008 (same applies to 2005) and have the following disaster recovery in place: * daily backup of each production database in file using a batch script * replication of production databases to backup server - failure of the production system only requires a config change in the web app.

SQL Server has an alert system to tell you if anything goes wrong. The impact on production has been minimal and our DRA is fairly strong given that we didn't pay anything extra. We have our data on 3+ physical locations and in the absolute worst case would only lose a day of data.

share|improve this answer
add comment

Any backup is only as reliable as the corresponding restore.

I've gotten live SQL2005 tables back using Backup Exec's agent, totally seamlessly and clean. It uses Volume Shadow Copy rather than the SQL APIs to accomplish it's job, but I expect that the SQL VSS writer just uses the APIs internally anyway (VSS is only really a common interface to backup and restore at the end of the day).

share|improve this answer
add comment

If you really want live SQL backup You can try with database replication

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.