Home / server / Questions / 59202
Accepted
Yishai
Asked: 2009-08-27 11:20:13 +0800 CST

SQL Server 2005: How reliable are live SQL Server backups?

  • 772

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?

sql-server

5 Answers

  1. Best Answer

    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.

    • 4

  2. 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.

    • 1

  3. 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.

    • 0

  4. 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).

    • 0

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

    • 0