Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am looking to do regular backups of my SQL Server databases.

Is there any reason why I should not just backup just the MSSQL.10/DATA folder? I've got a great backup solution in place from Rackspace which does incremental change based backups.

share|improve this question
add comment

migrated from stackoverflow.com Aug 1 '12 at 0:24

This question came from our site for professional and enthusiast programmers.

3 Answers

If those databases are in use, then the files may be locked and might be skipped by the backup process

share|improve this answer
    
Had a feeling this could be the case, going to go with Maintenance Plan backups then use Rackspace to back those up. –  bExplosion Jul 30 '12 at 7:37
    
Some backup products can read SQL databases and back them up correctly, but not all. Make sure you run your SQL Backup before the scheduled file backup to reduce the latency between the two. –  podiluska Jul 30 '12 at 8:14
add comment

I'm guessing you are referring to a file system level backup of your data folder?

I am not familar with Rackspace for performing such a task, but have worked with other backup products that allow you to take snapshots of active or inuse files. Which it would allow you to grab a picture of the data and log files of a SQL Server database.

The problem with that is when you take a backup of those files in this manner you want to make sure the database is in a consistent state so it can be recovered, or restored. When you take a snapshot of those files while a transaction is being written to the log file, or committed to the data file, it will not necessarily have a consistent state.

When SQL Server attaches or starts a database it will read in the log file and either roll forward or back any active transactions that have not been committed to the database. If it hits an inconsistent transaction it will mark the database as suspect and you have to clean it up manually, and hope you can get it online.

Another problem that will come up with not using SQL Server native backups can depend on your recovery model. The recovery model basically dictates how your log file is managed for reuse of space. If you are in full recovery model a native full backup helps keep the log under control, taking filesystem level backups will bypass that process so you could end up with a log file out of control.

NOTE: When you determine your backup strategy for your database, you must be confident that you can use it to restore your data. No matter what suggestions you get, always try your backup plan and do a test run on your restore. If you can restore with that backup plan and it meets your recovery needs, then go with it.

share|improve this answer
    
FYI, rackspace is a hosting provider. –  podiluska Jul 30 '12 at 8:18
add comment

In case you have SQL Backup database and it undergoes corruption case which you need to solve and thus you can visit here : http://sqlbackuprecoverytool.devhub.com/ it will help you to fix your corruption issue in .bak files of SQL Server.

share|improve this answer
add comment

Your Answer

 
discard

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